61

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?

Kenneth
  • 28,294
  • 6
  • 61
  • 84
  • How many ids are you passing when getting the 2000+ sql? – Ivo Feb 08 '14 at 03:47
  • For those of you using EF Core 3.1+, this provides a solution to this issue: https://stackoverflow.com/a/70587979/2206145 – yv989c Jan 08 '22 at 00:06

3 Answers3

110

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.

Maximilian Peters
  • 30,348
  • 12
  • 86
  • 99
Ivo
  • 8,172
  • 5
  • 27
  • 42
  • 7
    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. – MMalke Feb 22 '19 at 11:07
13

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.

Timeout
  • 7,759
  • 26
  • 38
  • 2
    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+ – Kenneth Feb 08 '14 at 02:22
  • @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. – Timeout Feb 08 '14 at 02:24
  • 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 – Kenneth Feb 08 '14 at 02:32
  • 1
    ?? It's really good yo use ORMs when they are useful. If you start having troubles, move to whatever is better. – Ivo Feb 08 '14 at 03:48
  • 1
    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.. – Dinerdo Mar 25 '19 at 20:18
  • @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. – Timeout Jul 03 '19 at 18:14
0

You may try this.

List<EnquirePriceSub> e = getSomethings();
var data = appDb.EnquirePriceSubs.Where(w=> e.Select(s=>s.Id).Contains(w.Id)).ToList();
รัฐพล.James
  • 61
  • 1
  • 7