LINQ to Entities .Contains for a tuple (2 foreign keys)

Question

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?

Answer 1

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.