ORDER BY C# Enum Flags most matches

user_983599 0
Viewed 213

Given the following Enum:

[Flags]
public enum CheeseCharacteristic 
{
    Yellow = 1,
    Stinks = 2,
    Squeaks = 4,
    Holey = 8,
    Mouldy = 16,
    UseOnToast = 32
}

And an entity Cheese having the property Characteristic, is it possible to order Cheeses based on how many flags are matched against the Characteristic? For example, say a Stilton has the Characteristic value Yellow, Stinks, Mouldy and a MildCheddar has the Characteristic value Yellow, UseOnToast, is it possible to order by the number of matches for a given query?

So if I want to compare a collection of Cheese entities against Yellow, Mouldy then both Stilton and MildCheddar are returned, but the Stilton is top of the list as it has 2 matches.

To take this further, if I store the enum value in SQL Server, can I perform this sort in the DB using EF Core?

entity-framework c#
2 Answers

I like this version because it maintains type safety while working with any bitmask enum.

using System;
using System.Linq;
using System.Collections.Generic;



namespace SO_58416947_count_bitmask_flags {
    static class Program {

        [Flags]
        public enum CheeseCharacteristics {
            Yellow = 1,
            Stinky = 2,
            Squeaky = 4,
            Holey = 8,
            Mouldy = 16,
            Spreadable = 32,
            UseOnToast = Yellow | Spreadable
        }


        static void Main(string[] args) {
            List<CheeseCharacteristics> _testCases = new List<CheeseCharacteristics>() {
                {CheeseCharacteristics.UseOnToast | CheeseCharacteristics.Stinky},
                {CheeseCharacteristics.Yellow | CheeseCharacteristics.Holey},   //  Swiss
                {CheeseCharacteristics.Squeaky},
                {CheeseCharacteristics.Mouldy | CheeseCharacteristics.Spreadable}
            };

            List<CheeseCharacteristics> cases = _testCases.OrderByFlagCount();

            foreach(CheeseCharacteristics c in cases) {
                Console.WriteLine($"{c}");
            }
        }   //  Main()



        private static List<T> GetIndividualFlagValues<T>() where T:Enum {
            Type enumType = typeof(T);

            if (!enumType.IsEnum) { throw new ArgumentException("Must pass an enum type."); }

            List<T> result = new List<T>();

            foreach(T item in Enum.GetValues(enumType)) {
                result.Add(item);
            }

            return result;
        }   //  GetIndividualFlagValues()



        //extension method
        public static List<T> OrderByFlagCount<T>(this List<T> list) where T:Enum {
            List<T> flags = GetIndividualFlagValues<T>();
            List<T> results = list
                .OrderBy(t => flags.Where(f => true == t.HasFlag(f)).Count())
                .ToList();

            return results;
        }

    }   //  Program
}       //  ns
user_740150

To do this in SQL you could (untested and not optimized):

Use the code on this blog entry to create a function to convert an int to a string representation of the binary representation of an int:

CREATE FUNCTION dbo.Int2Binary (@i INT) RETURNS NVARCHAR(16) AS BEGIN
    RETURN
        CASE WHEN CONVERT(VARCHAR(16), @i & 32768 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 16384 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  8192 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  4096 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  2048 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  1024 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   512 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   256 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   128 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    64 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    32 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    16 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     8 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     4 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     2 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     1 ) > 0 THEN '1' ELSE '0'   END
END;
GO

And follow this answer to create a function to count the occurrences of a string in a substring.

CREATE FUNCTION dbo.CountOccurrencesOfString
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END

And then mash them together like peanut butter and jelly:

SELECT * FROM Cheeses
ORDER BY CountOccurrencesOfString('1', Int2Binary(Characteristics))
user_740150
Related