LINQ to Entities - 具有多列的 where..in 子句

Question

来查询表单的数据

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

我正在尝试使用 LINQ-to-EF:通过按元组（国家、城市、地址）查找位置 。我尝试过

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
             }

，但 LINQ 不想接受匿名类型（我理解这是在 LINQ 中表达元组的方式）作为 Contains() 的参数。

有没有一种“好”的方式在 LINQ 中表达这一点，同时能够在数据库上运行查询？或者，如果我只是迭代键和 Union() 一起查询，这会对性能不利吗？

Answer 1

我将用更广泛的 IEnumerable 的 Any 扩展方法替换 Contains （这是一种特定于列表和数组的方法）：

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

也可以这样写：

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;

Answer 2

怎么样：

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

更新

不幸的是，EF 会抛出 NotSupportedException，如果您需要查询在数据库端运行，这会取消此答案的资格。

更新2

尝试使用自定义类和元组进行各种连接 - 都不起作用。我们谈论的数据量是多少？如果它不是太大，您可以在客户端处理它（方便）或使用联合（如果不是更快，至少传输的数据更少）。

Answer 3

虽然我无法让@YvesDarmaillac 的代码工作，但它向我指出了这个解决方案。

您可以构建一个表达式，然后分别添加每个条件。为此，您可以使用Universal PredicateBuilder（源代码在最后）。

这是我的代码：

// 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);
}

不过，需要注意的一件事是过滤器列表（本例中的 keys 变量）不能太大，否则您可能会达到参数限制，但有例外像这样：

SqlException：传入请求参数过多。服务器最多支持2100个参数。减少参数数量并重新发送请求。

因此，在此示例中（每行三个参数），要过滤的位置不能超过 700 个。

使用两项进行过滤，最终的SQL中会生成6个参数。生成的 SQL 如下所示（格式更清晰）：

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'

请注意 EntityFramework 如何正确忽略初始“false”表达式，并且不将其包含在最终 SQL 中。

最后，这是 Universal PredicateBuilder 的代码，用于记录。

/// <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);
        }
    }
}

Answer 4

我的解决方案是构建一个新的扩展方法WhereOr，它使用ExpressionVisitor来构建查询：

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;
    }
}

以下生成在数据库上执行的干净的sql代码：

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

Answer 5

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

需要是：

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

Answer 6

存在一个 EF 扩展，其设计用于非常相似的情况。它是 EntityFrameworkCore.MemoryJoin （名称可能会令人困惑，但它支持 EF6 和 EF核）。正如作者的文章 它修改传递到服务器的 SQL 查询，并使用本地列表中的数据注入 VALUES 构造。并且查询是在DB服务器上执行的。

所以对于你的情况，用法可能是这样的

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

Answer 7

您是否尝试过仅使用 Tuple 类？

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

Answer 8

如果您不需要大量组合键，则只需向数据添加 LocationKey 属性即可。为了避免浪费大量存储空间，可以将其设为组合属性的哈希码。

然后查询将仅在 LocationKey 上有一个条件。最后，在客户端过滤结果以删除具有相同哈希但位置不同的实体。

它看起来像：

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;
    …
}

然后简单地查询 LocationKey 属性。

不理想，但应该可以。

Answer 9

我认为这对您不起作用，因为当您在 Contains 方法中新建对象时，它每次都会创建一个新对象。由于这些对象是匿名的，因此将它们与它们的引用进行比较的方式对于每个对象来说都是不同的。

另外，看看雅切克的回答。

Answer 10

    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

尝试一下。

Answer 11

我认为正确的方法是

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

它看起来未优化，但查询提供程序将在将查询转换为 sql 时进行优化。当使用元组或其他类时，查询提供程序不知道如何将它们转换为 sql 以及导致 NotSupportedException 的原因

-编辑-

如果您有多个关键元组，我认为您必须循环遍历它们并对每个元组执行上述查询。同样，这可能看起来没有优化，但是在单个查询中检索所有位置的查询可能会变得很长：

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 ...

最快的方法可能是执行简单的查询，但将它们作为单个 sql 脚本发送，使用多个结果集来实际获取每个值。不过我不确定你能否让 EF 做到这一点。

Answer 12

您可以投影字符串连接键并在投影上进行匹配。但是，请注意，您将无法使用在列上构建的任何索引，并且将进行字符串匹配，这可能会很慢。

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

Answer 13

如何使用基于多列的 LINQ to SQL 检查是否存在

考虑：

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

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

您应该执行如下操作：

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

这将生成以下 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])))