检查列表包含在其他的EntityFramework列表项 [英] 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)));

这对小名单（比如5-10 searchIds和5-10位置的人工作得很好。问题是，有些人可能有100个地点和搜索，也可以在一次100的位置。当我试图执行上述EF竟产生了2000+ SQL语句失败，因为它被嵌套太深，而嵌套。本身已经有问题，即使它会工作，我仍然不十分正好与2000 + SQL语句

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.

请注意：真正的代码还包括多个水平和亲子关系，但我还是设法只使用ID的得到它下降到这个相当扁平结构，而不是完整的对象。

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

什么将是最好的办法？在EF做到这一点。

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

推荐答案

我会建议：

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

含将被转换为在语句。

请记住，ID列表进入SQL语句。如果你的ID列表是巨大的，那么你会最终有一个巨大的查询。

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.

这篇关于检查列表包含在其他的EntityFramework列表项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！