Entity Framework Core where clause with multiple values

Question

I have a table Person with few fields is it. I would like to find multiple people from the DB in one call without putting lots of 'OR's. When I tried the following query I got this error.

var list = new List<string>(){"John", "Adam", "Bill", "David"};
context.People.Where(p=>list.Any(l=>l == p.Name)).ToList();

error message:

System.InvalidOperationException: 'The LINQ expression 'DbSet .Where(p => __list_0 .Any(l => l == p.Name))' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

I guess my query cannot be translated, but is there any other way of doing it?

Thanks in advance!

Answer 1

The solution is using a function that can be translated into SQL,

var list = new List<string>(){"John", "Adam", "Bill", "David"};
var matchingPeople = context.People.Where(p=>list.Contains(p.Name)).ToList();

The List<string>.Contains() is translated into a SQL IN (values) sentence.

Note: As @MongZhu points out, in Entity Framework Core 3.0 there is a bug in that translation and IEnumerable.Contains() must be used instead. That bug was solved in 3.1

Answer 2

Have you try replacing .Any() for .Contains()? Something like this:

var list = new List<string>(){"John", "Adam", "Bill", "David"};
context.People.Where(p => list.Contains(p.Name)).ToList();