Any on enums inside of Where in Entity Framework Core

Question

I keep getting Entity Framework errors on this snippet of code (Consistency type is an enum):

IQueryable<Examination> examinationsSet = _context.Examinations;

if (consistency.Length > 0)
{
    examinationsSet = examinationsSet
                          .Where(x => consistency.Any(y => (int)y == (int)x.Consistency));
}

I tried adding AsQueryable or AsEnumerable between consistency and Any, but it doesn't help. This is the main error I am getting:

System.InvalidOperationException: „The LINQ expression 'Where(
source: DbSet,
predicate: (e) => Any(
source: (Unhandled parameter: __consistency_0),
predicate: (y) => (int)y == (int)e.Consistency))'
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().

What is the definition of Consistency? And how is EF supposed to write SQL that casts one to an int? — David Browne - Microsoft
Sorry, I haven't clarified that this consistency thing is of type Consistency which is an enum value. My mistake! — mikes
Tried .Where(x => consistency.Contains(x.Consistency)) ? Translates to SQL for int arrays in 3.1 but haven't yet tried with enum. — joakimriedel
Also, if this is 3.0 you'd better upgrade to 3.1 preview builds since 3.0 has a lot of troubles with LINQ-queries that used to work in 2.2.x — joakimriedel

Merkle Groot Merkle Groot · Accepted Answer · 2019-09-29T02:26:57

When an Entity Framework query executes, it's translated to SQL. It's best to do as much of the logic as possible outside of the query to avoid confusing the translator.

Try something like this instead:

List<int> consistencyNumbers = consistency.Select(item => (int)item).ToList();  
IQueryable<Examination> examinationsSet = consistencyNumbers.Count > 0
    ? _context.Examinations.Where(x => consistencyNumbers.Any(y => y == (int)x.Consistency))
    : _context.Examinations;

Any on enums inside of Where in Entity Framework Core

1 Answers