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?

Solution 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.

Solution 2

Try switching to joins instead of doing a massive data include:

var searchIds = new List<int>{1,2,3,4,5};
var results = (from p in persons
               join l in Location on p.PersonId equals l.PersonId
               where searchIds.Contains(l.Id)
               select p).Distinct().ToList();

Obviously fix this line to match your classes and/or join property.

join l in Location on p.PersonId equals l.PersonId

I would expect that to generate a more friendly execution plan.

Comments

[Kenneth]

I'm with you on the ORM's, so that'll be a fast discussion :-) Unfortunately I can't use joins because I don't have the Location set (Working with generic repositories). I did find that doing searchIds.Contains(l.Id) is a lot better than searchIds.Any(id => l.Id == id) though. Down to about 70 lines of SQL, not super but better than 2000+

[Timeout]

@Kenneth If you can see the Locations property inside the Person class I don't see why you wouldn't have access to query that class directly. Perhaps I'm not understanding the situation correctly. In any case I wish you luck.

[Kenneth]

Well, I only have direct access to one EntitySet (Persons). The rest is supposed to be queried indirectly through that class. Supposedly EF handles the joins and figures out the best execution path. Supposedly ... Anyway, the contains seems to be the most important part. Thanks for your help

[Ivo]

?? It's really good yo use ORMs when they are useful. If you start having troubles, move to whatever is better.

[MMalke]

Isn't there a solution for cases in which I have 2000+ ids in my search list? For example, in SQL I could create a temporary table, store the ids in it, and then use it in a JOIN clause.

[Dinerdo]

I'm using a generic repository pattern and I'm not sure why you wouldn't be able to do a .Join() with your implementation. Granted, it's now 5 years later..

[Timeout]

@Dinerdo This syntax and the .Join() method do the exact same thing. This way is just more readable IMO. I still prefer this syntax for anything other than a very basic query.