LINQ to Entities - where..in clause with multiple columns
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).
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;
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
)
);