DayOfWeek in LINQ query

user_4546511 0
Viewed 765

I have a simple list of integers that represent days of week. I am trying to check if Date property of my entity is in the selected days of week. But if I try to pass it in query that targets database like this:

query.Where(e => selectedDaysOfWeek.Contains((int)e.Date.DayOfWeek));

I got the exception:

The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

If I on the other hand, first execute query by calling ToList() (for example) and then add the same where condition on resulting list, it works:

var items = query.ToList();
items = items.Where(e => selectedDaysOfWeek.Contains((int)e.Date.DayOfWeek)).ToList();

Although in my case this is acceptable, I would like to fetch less items from the database. Is there a way to check DayOfWeek when querying db as was my initial intent?

ef-core-3.1 entity-framework-core linq
1 Answers

You are accessing the DayOfWeek property from a DateTime reference in the query.

Entity Framework "doesn't know" how to translate that to SQL thus in the first piece of code you getting an exception.

And in the second piece it is working after you have fetch all of the data from database at the .ToList() call, and the .Where filtering is happening in the memory.

If you wish to implement that logic on the database side, you will have to write your own SQL statement.

user_56461430
Related