Check if list contains item from other list in EntityFramework

Question

I have an entity Person which has a list of locations associated with it. I need to query the persons table and get all those that have at least one location from a list of locations (criteria). The following works but is highly inefficient:

var searchIds = new List<int>{1,2,3,4,5}; var result = persons.Where(p => p.Locations.Any(l => searchIds.Any(id => l.Id == id))); 

This works fine for small lists (say 5-10 searchIds and a person with 5-10 locations. The issue is that some persons may have 100 locations and a search can also be for 100 locations at once. When I tried to execute the above EF actually produced a 2000+ SQL statement and failed because it was too deeply nested. While the nesting is already a problem in itself, even if it would work, I'd still not be very happen with a 2000+ SQL statement.

Note: the real code also includes multiple levels and parent-child relations, but I did manage to get it down to this fairly flat structure using only id's, instead of full objects

What would be the best way to accomplish this in EF?

Answer 1

I'll suggest:

var searchIds = new List<int>{1,2,3,4,5}; var result = persons.Where(p => p.Locations.Any(l => searchIds.Contains(l.Id))); 

Contains will be translated to IN statement.

Keep in mind that the id list goes into the sql statement. If your id list is huge then you'll end up having a huge query.