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

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 be开发者_如何学运维ing 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?

How about:

var result = locations.Where(l => keys.Any(k => 
                    k.Country == l.Country && 
                    k.City == l.City && 
                    k.Address == l.Address));

UPDATE

Unfortunately EF throws NotSupportedException on that, which disqualifies this answer if you need the query to run on DB side.

UPDATE 2

Tried all kinds of joins using custom classes and Tuples - neither works. What data volumes are we talking about? If it's nothing too big, you could either process it client-side (convenient) or use unions (if not faster, at least less data is transmitted).

Although I couldn't get @YvesDarmaillac's code to work, it pointed me to this solution.

You can build an expression and then add each condition separately. To do this, you can use the Universal PredicateBuilder (source at the end).

Here's my code:

// First we create an Expression. Since we can't create an empty one,
// we make it return false, since we'll connect the subsequent ones with "Or".
// The following could also be: Expression<Func<Location, bool>> condition = (x => false); 
// but this is clearer.
var condition = PredicateBuilder.Create<Location>(x => false);

foreach (var key in keys)
{
    // each one returns a new Expression
    condition = condition.Or(
        x => x.Country == key.Country && x.City == key.City && x.Address == key.Address
    );
}

using (var ctx = new MyContext())
{
    var locations = ctx.Locations.Where(condition);
}

One thing to beware of, though, is that the filter list (the keys variable in this example) can't be too large, or you may reach the parameters limit, with an exception like this:

SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

So, in this example (with three parameters per line), you can't have more than 700 Locations to filter.

Using two items to filter, it will generate 6 parameters in the final SQL. The generated SQL will look like below (formatted to be clearer):

exec sp_executesql N'
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Country] AS [Country], 
    [Extent1].[City] AS [City], 
    [Extent1].[Address] AS [Address]
FROM [dbo].[Locations] AS [Extent1]
WHERE 
    (
        (
            ([Extent1].[Country] = @p__linq__0) 
            OR 
            (([Extent1].[Country] IS NULL) AND (@p__linq__0 IS NULL))
        )
        AND 
        (
            ([Extent1].[City] = @p__linq__1) 
            OR 
            (([Extent1].[City] IS NULL) AND (@p__linq__1 IS NULL))
        ) 
        AND 
        (
            ([Extent1].[Address] = @p__linq__2) 
            OR 
            (([Extent1].[Address] IS NULL) AND (@p__linq__2 IS NULL))
        )
    )
    OR
    (
        (
            ([Extent1].[Country] = @p__linq__3) 
            OR 
            (([Extent1].[Country] IS NULL) AND (@p__linq__3 IS NULL))
        )
        AND 
        (
            ([Extent1].[City] = @p__linq__4) 
            OR 
            (([Extent1].[City] IS NULL) AND (@p__linq__4 IS NULL))
        ) 
        AND 
        (
            ([Extent1].[Address] = @p__linq__5) 
            OR 
            (([Extent1].[Address] IS NULL) AND (@p__linq__5 IS NULL))
        )
    )
',
N'
    @p__linq__0 nvarchar(4000),
    @p__linq__1 nvarchar(4000),
    @p__linq__2 nvarchar(4000),
    @p__linq__3 nvarchar(4000),
    @p__linq__4 nvarchar(4000),
    @p__linq__5 nvarchar(4000)
',
@p__linq__0=N'USA',
@p__linq__1=N'NY',
@p__linq__2=N'Add1',
@p__linq__3=N'UK',
@p__linq__4=N'London',
@p__linq__5=N'Add2'

Notice how the initial "false" expression is properly ignored and not included in the final SQL by EntityFramework.

Finally, here's the code for the Universal PredicateBuilder, for the record.

/// <summary>
/// Enables the efficient, dynamic composition of query predicates.
/// </summary>
public static class PredicateBuilder
{
    /// <summary>
    /// Creates a predicate that evaluates to true.
    /// </summary>
    public static Expression<Func<T, bool>> True<T>() { return param => true; }

    /// <summary>
    /// Creates a predicate that evaluates to false.
    /// </summary>
    public static Expression<Func<T, bool>> False<T>() { return param => false; }

    /// <summary>
    /// Creates a predicate expression from the specified lambda expression.
    /// </summary>
    public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }

    /// <summary>
    /// Combines the first predicate with the second using the logical "and".
    /// </summary>
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.AndAlso);
    }

    /// <summary>
    /// Combines the first predicate with the second using the logical "or".
    /// </summary>
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.OrElse);
    }

    /// <summary>
    /// Negates the predicate.
    /// </summary>
    public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
    {
        var negated = Expression.Not(expression.Body);
        return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
    }

    /// <summary>
    /// Combines the first expression with the second using the specified merge function.
    /// </summary>
    static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        // zip parameters (map from parameters of second to parameters of first)
        var map = first.Parameters
            .Select((f, i) => new { f, s = second.Parameters[i] })
            .ToDictionary(p => p.s, p => p.f);

        // replace parameters in the second lambda expression with the parameters in the first
        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

        // create a merged lambda expression with parameters from the first expression
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    class ParameterRebinder : ExpressionVisitor
    {
        readonly Dictionary<ParameterExpression, ParameterExpression> map;

        ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;

            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }

            return base.VisitParameter(p);
        }
    }
}

My solution is to build a new extension method WhereOr which use an ExpressionVisitor to build the query :

public delegate Expression<Func<TSource, bool>> Predicat<TCle, TSource>(TCle cle);

public static class Extensions
{
    public static IQueryable<TSource> WhereOr<TSource, TCle>(this IQueryable<TSource> source, IEnumerable<TCle> cles, Predicat<TCle, TSource> predicat)
        where TCle : ICle,new()
    {
        Expression<Func<TSource, bool>> clause = null;

        foreach (var p in cles)
        {
            clause = BatisseurFiltre.Or<TSource>(clause, predicat(p));
        }

        return source.Where(clause);
    }
}

class BatisseurFiltre : ExpressionVisitor
{
    private ParameterExpression _Parametre;
    private BatisseurFiltre(ParameterExpression cle)
    {
        _Parametre = cle;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return _Parametre;
    }

    internal static Expression<Func<T, bool>> Or<T>(Expression<Func<T, bool>> e1, Expression<Func<T, bool>> e2)
    {
        Expression<Func<T, bool>> expression = null;

        if (e1 == null)
        {
            expression = e2;
        }
        else if (e2 == null)
        {
            expression = e1;
        }
        else
        {
            var visiteur = new BatisseurFiltre(e1.Parameters[0]);
            e2 = (Expression<Func<T, bool>>)visiteur.Visit(e2);

            var body = Expression.Or(e1.Body, e2.Body);
            expression = Expression.Lambda<Func<T, bool>>(body, e1.Parameters[0]);
        }

        return expression;
    }
}

The following generates clean sql code executed on database :

var result = locations.WhereOr(keys, k => (l => k.Country == l.Country && 
                                                k.City == l.City && 
                                                k.Address == l.Address
                                          )
                          );

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

would need to be:

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

There is an EF extension exists which was designed to very similar case. It is EntityFrameworkCore.MemoryJoin (name might be confusing, but it supports both EF6 and EF Core). As stated in author's article it modifies SQL query passed to server and injects VALUES construction with data from your local list. And query is executed on DB server.

So for your case usage might be like this

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

// here is the important part!
var keysQueryable = context.FromLocalList(keys);

var result = from loc in Location
    join key in keysQueryable on new { loc.Country, loc.City, loc.Address } equals new { key.Country, key.City, key.Address }
    select loc

Have you tried just using the Tuple class?

var keys = new[] {
    Tuple.Create("Country", "City", "Address"),
    …
}

var result = from loc in Location
             where keys.Contains(Tuple.Create(loc.Country, loc.City, loc.Address))

If you're not going to need a lot of key combinations, you can simply add a LocationKey property to your data. To avoid wasting a lot of storage, maybe make it the hash code of the combined properties.

Then query on will simply have a condition on LocationKey. Finally, in the client side filter the results to drop entities that had the same hash but not the same location.

It would look something like:

class Location 
{
    private string country;
    public string Country
    {
        get { return country; }
        set { country = value; UpdateLocationKey(); }
    }

    private string city;
    public string City
    {
        get { return city; }
        set { city = value; UpdateLocationKey(); }
    }

    private string address;
    public string Address
    {
        get { return address; }
        set { address = value; UpdateLocationKey(); }
    }

    private void UpdateLocationKey()
    {
        LocationKey = Country.GetHashCode() ^ City.GetHashCode() ^ Address.GetHashCode();
    }

    int LocationKey;
    …
}

Then simply query on the LocationKey property.

Not ideal, but it should work.

I don't think that will work for you since when you are newing up an object in the Contains method it will create a new object each time. Since those object are anonymous the way they will be compared are against their reference which will be different for each object.

Also, look at Jacek's answer.

    var keys = new[] {
        new {Country=…, City=…, Address=…},
        …
    }    
    var result = from loc in Location
                 where keys.Any(k=>k.Country == loc.Country 
&& k.City == loc.City 
&& k.Address == loc.Address) 
select loc

Give this a try.

i think the proper way to do it is

var result = from loc in Location
             where loc.Country = _country
             where loc.City = _city
             where loc.Address = _address
             select loc

It looks unoptimized but the query provider will go out and do the optimization when it transforms the query to sql. When using tuples or other classes, the query provider doesnt know how to transform them into sql and that what causes the NotSupportedException

-edit-

If you have multiple key tuples i think you have to loop through them all and do the above query for each one. again, that might seem underoptimized, but the query for retriving all the locations in a single query would probably end up beeing quite long:

select * from locations 
where (locations.Country = @country1 and locations.City = @city1, locations.Adress = @adress1)
or (locations.Country = @country2 and locations.City = @city2, locations.Adress = @adress2)
or ...

The fastest way of doing it is probably to do the simple queries, but send them as a single sql script and use multiple result sets for actually getting each value. Im not sure you can get EF to do that though.

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();

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])))

I'd replace Contains (which is a method specific to lists and arrays) with the wider IEnumerable's Any extension method:

var result = Location
    .Where(l => keys.Any(k => l.Country == k.Country && l.City = k.City && l.Address == k.Address);

This can also be written:

var result = from l in Location
             join k in keys
             on l.Country == k.Country && l.City == k.City && l.Address == k.Address
             select l;