Linq to SQL: Compare In-Memory Tuple List Array with Table

Question

I need to compare the in-memory tuples with table. I tried with the following query and it is not working,

var tuples = new List<Tuple<string, int>>()
            {
                new Tuple<string, int>("12222",1),
                new Tuple<string, int>("12222",2)
            };

var result = Context.infotable
      .Where(i => tuples.Any(t => t.Item1 == i.col1 && t.Item2 == i.col2)
      .ToList();

Following exception is thrown:

Unable to create a constant value of type 'System.Tuple`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types or enumeration types are supported in this context.

Answer 1

The error says that Linq to SQL can't translate that expression that uses the Tuple type into an SQL construct.

You could try to build the query in a different way, with a bit of help from PredicateBuilder

var predicate = PredicateBuilder.False<infotable>();
foreach (var tuple in tuples)
{
   var item1 = tuple.Item1;
   var item2 = tuple.Item2;
   predicate = predicate.Or(t => t.Item1 == item1 && t.Item2 == item2);
}

var result = Context.infotable.Where(predicate).ToList();

I would pay extra attention if the tuples list is large enough to cause SQL performance problems.

Answer 2

I believe that means Tuple can't be translated to SQL. I would break out the types from the Tuple and test each:

var t1s = tuples.Select(t => t.Item1).ToArray();
var t2s = tuples.Select(t => t.Item2).ToArray();
var result = Context.infotable
    .Where(i => t1s.Any(t1 => t1 == i.col1) && t2s.Any(t2 => t2 == i.col2))
    .ToList();

It would not surprise me if Any won't work with a local sequence and you must use Contains:

var result = Context.infotable
    .Where(i => t1s.Contains(i.col1) && t2s.Contains(i.col2))
    .ToList();

Answer 3

This happens because in the query you are use Tuples.

As example this construction should work:

var firstItems = tuples.Select(t => t.Item1);
var secondItems = tuples.Select(t => t.Item2);

var result = Context.infotable
                    .Where(i => firstItems.Any(t => t == i.col1) && secondItems(t2 => t2 == i.col2))
      .ToList();