title: Check if list contains item from other list in EntityFramework
body: <p>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:</p>
  
  <pre><code>var searchIds = new List&lt;int&gt;{1,2,3,4,5};
  var result = persons.Where(p =&gt; p.Locations.Any(l =&gt; searchIds.Any(id =&gt; l.Id == id)));
  </code></pre>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>What would be the best way to accomplish this in EF?</p>