Using contains to match two fields

Question

I have a database table which has a bunch of fields including one called Type and another called Code. I also have a list of Type and Code pairs that are encapsulated in a class. Something like this:

public class TypeAndCode
{
    public byte Type { get; set; }
    public int Code { get; set; }

    // overrides for Equals and GetHashCode to compare if Type and Code are equal
}

Now what I need to do is select from the table only those entries who type AND code match an entry in my collection. So, for example, I tried something like this:

var query = myTable.Where(a => myTCList.Contains(new TypeAndCode() { Type = a.Type, Code = a.Code }).ToList();

But it'll give me a NotSupportedException:

Unable to create a constant value of type 'TypeAndCode'. Only primitive types 
or enumeration types are supported in this context.

Is there a way to make this work so that I can retrieve from the database only those entries that have a Code and Type that match my list of Code and Type? I'm trying to avoid having to retrieve all the entries (it's a big table) and match them in memory.

I'm aware that I could try something like

var query = myTable.Where(a => listOfTypes.Contains(a.Type) && listOfCodes.Contains(a.Codes))

But that will make some spurious matches where the type and code are from different pairs in my original list.

+1 I'm rally not an expert in database driven LINQ providers, but obviously this cannot be translated to sql. The problem is that a local collection can be used only with `Contains` and only if it contains only primitive types like `int`, `DateTime` or `string`. Your class contains two properties and LINQ does not know what to do. You could use `.Where(a => myTCList.Select(tc => tc.Type).Contains(a.Type) && myTCList.Select(tc => tc.Code).Contains(a.Code))`. But that would be somewhat inefficient. You're also aware of that as mentioned. — Tim Schmelter, Aug 20 '14 at 20:42

score 1 · Answer 1 · answered Aug 20 '14 at 15:43

1

You can use Any instead:

var query = myTable
    .Where(a => myTCList.Any(t => t.Type == a.Type && t.Code == a.Code ))
    .ToList();

answered Aug 20 '14 at 15:43

Tim Schmelter

450,073
74
686
939

Actually, that was what I originally tried, but I get the same `NotSupportedException`. – Matt Burland Aug 20 '14 at 15:51
What Linq Provider are you using at all? It seems that its not able to tranlate it to sql since it is a complex object with multiple properties. You could add AsEnumerable to read all into memory first if the table is not large. Then your Contains check should work also because you have overridden Equals. – Tim Schmelter Aug 20 '14 at 16:15
I'm pulling from a large table in sql server, so I really wanted to try and find a way to avoid pulling *everything* if possible. The problem of converting it to sql is understandable, I was looking to see if there's a work around. It's looking like the best I can hope for is to ensure this is the last filter so I can cut down the list as much as possible before applying this in memory. – Matt Burland Aug 20 '14 at 16:35
@MattBurland: i don't know a better method than the one you've already metioned(_"I'm aware that I could try..."_). However, why do you need to filter by a local collection at all? – Tim Schmelter Aug 20 '14 at 20:52
The idea is that the user would select a set of code/type pairs and I will need to retrieve all entries from the table that match one of those code/type pairs. Maybe going about it the wrong way? Perhaps I need to write a temporary table of something so I can just join it? – Matt Burland Aug 20 '14 at 21:00
@MattBurland: how long can this list be? I mean, if it's not that long you could really use the double-Contains check. Actually this is tranlsated to an `IN`-clause in sql, so it is [limited](http://stackoverflow.com/questions/1069415/limit-on-the-where-col-in-condition). But with less than 1000 i don't see a problem. Have you tested it? – Tim Schmelter Aug 20 '14 at 21:54

score 1 · Answer 2 · answered Aug 20 '14 at 15:44

1

You should be able to just do this manually without the overloaded methods from your class:

myTCList.Any(x => x.Type == a.Type && x.Code == a.Code)

answered Aug 20 '14 at 15:44

nvoigt

75,013
26
93
142

Doesn't `Any` return a `bool` instead of a list of matching entries? ([Documentation](http://msdn.microsoft.com/en-us/library/vstudio/bb534972(v=vs.100).aspx)) – Jeff B Aug 20 '14 at 18:43
@JeffBridgman Indeed, exactly like Contains that was to be replaced. – nvoigt Aug 20 '14 at 19:39

score 0 · Accepted Answer · answered Oct 30 '14 at 15:26

My ulitmate solution here, in case anybody else encounters a similar problem, was to set up a temporary table that I could write the pairs I wanted to match to which I could them join with the database table. After doing the join and materializing the results, you can delete the temporary table.

Something like:

ctx.myTempTable = (from pair in mypairs
                   select new myTempTable() { Type = pair.Type, Code = pair.Code }).ToList();
ctx.SaveChanges();

var query = from q in myTable 
            join t in ctx.myTempTable
            on new { q.Type, q.Code } equals new { t.Code, t.Type }
            select q;

The whole thing is in a try/catch/finally block with the finally block used to clear up the temporary table(s)

Using contains to match two fields

3 Answers3