Entity Framework Core where clause with multiple values

4
votes

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!

2
c#linqentity-framework-core
Quick but not usefull answer would be "using an expression that could be translated". Joke aside, IIRC list.Contains(p.Name) should work. - Cleptus
contains will be translated into WHERE Name IN ('John', 'Adam', ...) - Mong Zhu
@Musulmon Yeah, that is the problem in fact, OP is asking what other expression would work. In this case it would be .Where(p=>list.Contains(p.Name)) and internally it would be translated to a IN (values) SQL clause - Cleptus
you need to be aware of a size limitation of your list here. 2100 Items maximum - Mong Zhu
what is the version of the core framework here? There is a funny trap with contains in 3.0 - Mong Zhu

2 Answers

4
votes

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

1
votes

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();