Actually, a colleague of mine asked me this question and I haven't been able to come up with an answer. Here goes.
Given an entity with 2 foreign keys, say
public class MyTable
{
public int Key1 { get; set; }
public int Key2 { get; set; }
}
and 2 lists
public ICollection List1 => new List<int> { 1, 2, 3 };
public ICollection List2 => new List<int> { 4, 5, 6 };
he needs to query for all records where Key1 matches the value from List1 and Key2 matches the value from List2, e.g.
Key1 == 1 && Key2 == 4
that is, he wants to check for any given tuple from List1 and List2, (1, 4), (2, 5) and (3, 6).
Is there a straightforward way in EF to do this?
You can make a for loop to capture some local variable (in each loop) in the Where and use Concat (or Union - maybe with worse performance) to sum up all the result like this:
IQueryable<MyTable> q = null;
//suppose 2 lists have the same count
for(var i = 0; i < list1.Count; i++){
var k1 = list1[i];
var k2 = list2[i];
var s = context.myTables.Where(e => e.Key1 == k1 && e.Key2 == k2);
q = q == null ? s : q.Concat(s);
}
//materialize the result
var result = q.ToList();
NOTE: we can use Concat here because each sub-result should be unique (based on searching the keys). It surely has better performance than Union (ensuring uniqueness while we already know the sub-results are all unique beforehand - so it's unnecessary).
If you have a list of int (just integral numeric), you can also pair the keys into underscore separated string and use Contains normally like this:
var stringKeys = list1.Select((e,i) => e + "_" + list2[i]).ToList();
var result = context.myTables.Where(e => stringKeys.Contains(e.Key1 + "_" + e.Key2))
.ToList();
Building an Expression tree is also another approach but it's more complicated while I'm not sure if it has better performance.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments