Use List<Tuple<int, int>> to return data in Linq

Question

Given:

List<int> myList;

If I wanted to return data where the record ID was contained in this list I would simply do:

var q = db.Table.Where(c=> myList.Contains(c.ID));

However, given:

List<Tuple<int, int>> myList;

How would I write a Linq query to return records where both conditions are met? With one data point I would write:

var q = db.Table.Where(c=>
            c.ID == myList.Item1
            && c.AnotherValue == myList.Item2);

How would I convert the above statement to work on a List<Tuple<int, int>>?

Answer 1

A Tuple is a structure that can't not be translated to sql by your Linq Provider. A solution could be making a switch to Linq to Objects

var q = db.Table.AsEnumerable()
                .Where(c=> myList.Any(tuple => c.ID == tuple.Item1 &&
                                           c.AnotherValue == tuple.Item2));

But the bad thing about this solution is that you're going to load all the rows from that table to filter in memory.

Another solution could be using Linqkit:

var predicate = PredicateBuilder.False<Table>();

foreach (string t in myList)
{
    predicate = predicate.Or(c =>c.ID == t.Item1 && c.AnotherValue == t.Item2));
}

db.Table.AsExpandable().Where(predicate);

You will find more info about this last solution in this link

Answer 2

var q = db.Table.AsEnumerable().Where(c => myList.Any(tuple => c.ID == tuple.Item1 &&
                                               c.AnotherValue == tuple.Item2));

With Any you can check if there is at least one element in myList the matches your condition.

But as @octaviocci pointed out, this is not translatable to SQL, so you would need to call AsEnumerable() before and do the filtering locally, which may not be what you want if there are a lot of irrelevant records.