CODEWITHSUNDEEP
c#databaselinqentity-framework
Kenneth
Kenneth

Reputation: 28737

Check if list contains item from other list in EntityFramework

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?

Upvotes: 63

Views: 167660

Answers (3)

รัฐพล.James
รัฐพล.James

Reputation: 99

You may try this.

List<EnquirePriceSub> e = getSomethings();
var data = appDb.EnquirePriceSubs.Where(w=> e.Select(s=>s.Id).Contains(w.Id)).ToList();

Upvotes: 1

Ivo
Ivo

Reputation: 8362

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.

Upvotes: 114

Timeout
Timeout

Reputation: 7909

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.

Upvotes: 13

Related Questions