Strange behavior when compare DateTime Ardalis.Specifications

Question

Next line works fine

Query.Where(s => s.Id == id && listOfDate.Any(d => d == s.Date));

but this one Query.Where(s => s.Id == id && listOfDate.Any(d => d > s.Date)); throw invalidOperationException which said that can`t translate query

if thats normal behavior please explain why second line doesnt work?

Answer 1

It's not strange. You are mixing database entities with listOfDate, which seems to be a local (in memory) collection. So the challenge is that Linq-To-Entities needs to find a way to translate that to valid sql.

The first query can be translated to a simple listOfDate.Contains(s.Date) which is supported by Linq-To-Entitities(can be translated to sql). The second is a more complex filter of the local collection which cannot be translated to sql.

A workaround would be to just filter by id in the database and the remaining filter in memory:

Query.Where(s => s.Id == id).AsEnumerable().Where(s => listOfDate.Any(d => d > s.Date));