ORDER BY C# Enum Flags most matches

Question

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?

Maybe you could create a function that counts set bits in an integer value and use that count to order by as solution to your first question. As for if and how this could be done in the DB using EF Core I have no idea... — , Oct 16 '19 at 15:43

score 0 · Answer 1 · answered Oct 16 '19 at 16:47

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

score 0 · Answer 2 · answered Oct 16 '19 at 17:00

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))

ORDER BY C# Enum Flags most matches

2 Answers2