LINQ to Entities - where..in clause with multiple columns

user_41655 0
Viewed 26147

I'm trying to query data of the form with LINQ-to-EF:

class Location {
    string Country;
    string City;
    string Address;
    …
}

by looking up a location by the tuple (Country, City, Address). I tried

var keys = new[] {
    new {Country=…, City=…, Address=…},
    …
}

var result = from loc in Location
             where keys.Contains(new {
                 Country=loc.Country, 
                 City=loc.City, 
                 Address=loc.Address
             }

but LINQ doesn't want to accept an anonymous type (which I understand is the way to express tuples in LINQ) as the parameter to Contains().

Is there a "nice" way to express this in LINQ, while being able to run the query on the database? Alternately, if I just iterated over keys and Union()-ed the queries together, would that be bad for performance?

where-in multiple-columns linq-to-entities c#
13 Answers

You can project a string concat key and match on the projection. However, do note that you will not be able to use any indexes built on the columns and will be doing a string match which could prove to be slow. 

var stringKeys = keys
    .Select(l => $"{l.Country}-{l.City}-{l.Address}")
    .ToList();

var result = locations
    .Select(l => new
    {
        Key = l.Country + "-" + l.City + "-" + l.Address)
    }
    .Where(l => stringKeys.Contains(l.Key))
    .ToList();
user_20338930

How to check if exists using LINQ to SQL based on multiple columns

Considering:

class Location {
    string Country;
    string City;
    string Address;
    …
}

var keys = new[] {
    new {Country=…, City=…, Address=…},
    …
}

You should do something like this:

from loc in Location where (
    from k in keys where k.Country==loc.Country && k.City==loc.City && k.Address=loc.Address select 1).Any()

Which will produce the following SQL:

FROM [Locations] AS [p0]
WHERE (NOT (EXISTS (
    SELECT 1
    FROM [Keys] AS [p1]
    WHERE [p0].[Country] = [p1].[Country]) AND ([p0].[City] = [p1].[City]) AND ([p0].[Address]=[p1].[Address])))
user_42213760
Related