Using a enum with flag in an Entity Framework query

Question

i have a enum type like below

  [Flags]
    public enum WeekDays
    {

        Monday = 1,
        Tuesday = 2,
        Wednesday = 4,
        Thursday = 8,
        Friday = 16,
        Saturday = 32,
        Sunday = 64,
    }


   WeekDays dw = WeekDays.Friday | WeekDays.Monday | WeekDays.Saturday;

   int dwInt = (int)dw;

   var list = query.Where(f => f.FromDateTime.DayOfWeek == dwInt "??????????? what can i do there????").ToList();

Carollcarolle

Are you aware that you did not ask anything? – Leopard

i wrote ??????????? what can i do there???? – Carollcarolle

@Carollcarolle Don't place your question within the code itself, especially when we will need to scroll in order to find it. – Whiffle

@JonathanSampson,thanks for advice. – Carollcarolle

Answer 1

solution is here

 public static Dictionary<int, int> map = new Dictionary<int, int>() { { 0, 64 }, { 1, 1 }, { 2, 2 }, { 3, 4 }, { 4, 8 }, { 5, 16 }, { 6, 32 } };

//actually,this comes from the user interface, but we should focus on the database layer
WeekDays[] dw = new WeekDays[] {WeekDays.Saturday,WeekDays.Tuesday,WeekDays.Wednesday };
    
int[] systemDayOfWeekList = new int[daysOfWeek.Length];
    
for (int i = 0; i < daysOfWeek.Length; i++)
{
  systemDayOfWeekList[i] = map.FirstOrDefault(e => e.Value == (int)daysOfWeek[i]).Key;
}
    
query = query.Where(f => dayOfWeekList.Contains(((int)SqlFunctions.DatePart("dw", f.FromDateTime))));

Carollcarolle

Answer 2

Starting from Entity Framework 6.1, you can use the HasFlag extension method in your requests.

For example:

query.Where(f => f.FromDateTime.DayOfWeek.HasFlag(WeekDays.Friday | WeekDays.Monday)).ToList();

See https://entityframework.codeplex.com/workitem/1497 for details about the feature request and implementation.

Busra

Really curious how migration would work on extending flag enum – Schuh

Alternate Link (because codeplex link is broken) learn.microsoft.com/de-de/dotnet/api/… – Thorvaldsen

Or if you don't speak German: learn.microsoft.com/dotnet/api/system.enum.hasflag?view=net-5.0 – Sure

Answer 3

I'm going to make a guess that you weren't sure what to put in the query to filter for the days you list in the source.

Given your source snippet, I think its safe to infer that dwInt is being used as a bitmask, and DayOfWeek will have one bit position "set" to indicate a given day of week. On that basis, what you want to do is in the Where is perform a logical bitwise AND on DayOfWeek field with dwInt, and then check the result to be greater than 0, implying one of the desired day of week "bits" is set in your target field. I believe this would do the trick:

var list = query.Where(f => (f.FromDateTime.DayOfWeek & dwInt) >0).ToList()

Please forgive if I have interpreted your question incorrectly.

Pogrom

Note that this won't work against an Entity Framework query as it won't translate to SQL. You would have to call ToList() and perform the Where() on the resulting in-memory collection. – Thies

Worked for me using EF 5, .NET 4.5 without .ToList() – Dabble

(i know i am necromanting this, but for random googlers like me: ) Worked for me using EF 5 - but your query is half-done at server & half-done locally (the part EF dont understand like bit operations, is done locally), which can harm performance pretty badly. And this behaviour is not supported in EF Core, there it throw exception to let you know you have to use .ToArray() and do it locally - so you can decide "thats ok" or "omg, thats bad, lets try to come up with different solution". – Ardennes

Answer 4

[HasFlags] attribute is very interesting in a sense that it does not affect anything but .ToString() and (AFAIK) Enum.Parse() operations. Therefore for non-string operations it does not matter whether your enum type has [HasFlags] attribute. The way enums work in EF is that they are just cast to the underlying type and are treated as if they were one of the following integral types int64, int32, int16, byte, sbyte (note unsigned integral types are not supported by EDM and therefore enums with unsigned underlying type won't work and also in the database enum columns are just columns of a type that correspond to one of the above types). This means that basically any operation that is valid on an integral number values (of type supported by EF) is valid on enum values unless the compiler does not allow it (I don't think I am aware of any operation like this). This also means that whatever you wanted to put where your ???? are should work if it compiles (Sql Server supports bitwise operations)

Flinders

Answer 5

I would normally do it in a way that I do not get the result in memory and then processes it in c#, I think that's not constructive and my database tables are too big for it to begin with.

What I do is I write a query and pass the enum flag as a SQL parameter, the SQL "[ColumnName] & @flag=@flag" is the same as in DotNet using "property.HasFlag(MyEnum.MyFlagValue)"

public async Task<ICollection<FrequentlyAskedQuestion>> ExecuteAsync(GeoLocation language, ProductGroups productGroup)
{
    var p1 = new SqlParameter("p1", (int)language);
    var flag = new SqlParameter("flag", (int)productGroup);
    using var db= await _contextFactory.CreateDbContextAsync();
    return await db.FrequentlyAskedQuestions
                .FromSqlRaw("select * from [dbo].[GetFrequentlyAskedQuestions](@p1) where ProductGroup & @flag = @flag", p1,flag)                     
                .OrderByDescending(d=>d.AnswerWasHelpfull)
                .AsNoTracking()
                .ToArrayAsync();
}

in the sample, I use a UDF that returns the data from the table in the user's language or in the default language. I then further filter it in the use case that requires flags to be applied.

I am not aware of the possibility for EF to generate such query for me, taking control and writing the query will do and any changes to the schema will result in the Unit Tests failing when schema changes are not propagated.

Decrepitude