+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+--- Thread: Check if list contains item from other list in EntityFramework (/Thread-Check-if-list-contains-item-from-other-list-in-EntityFramework)
Check if list contains item from other list in EntityFramework - unfallen41189 - 07-20-2023
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?
RE: Check if list contains item from other list in EntityFramework - imago527 - 07-20-2023
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.
RE: Check if list contains item from other list in EntityFramework - helly677669 - 07-20-2023
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.
RE: Check if list contains item from other list in EntityFramework - violettejhlszalki - 07-20-2023
You may try this.
List<EnquirePriceSub> e = getSomethings();
var data = appDb.EnquirePriceSubs.Where(w=> e.Select(s=>s.Id).Contains(w.Id)).ToList();