实体框架 - 包含复合键的查询

本文关键字:查询 复合 包含 框架 实体 | 更新日期: 2023-09-27 18:25:38

给定一个 id 列表,我可以通过以下方式查询所有相关行:

context.Table.Where(q => listOfIds.Contains(q.Id));

但是,当表具有组合键时,如何实现相同的功能?

实体框架 - 包含复合键的查询

这是一个令人讨厌的问题,我不知道任何优雅的解决方案。

假设您有这些组合键,并且您只想选择标记的组合 (*(。

Id1  Id2
---  ---
1    2 *
1    3
1    6
2    2 *
2    3 *
... (many more)

如何做到这一点是实体框架满意的一种方式?让我们看看一些可能的解决方案,看看它们是否有任何好处。

解决方案 1:成对Join(或Contains(

最好的解决方案是创建一个你想要的对的列表,例如元组,(List<Tuple<int,int>>(,并将数据库数据与此列表连接起来:

from entity in db.Table // db is a DbContext
join pair in Tuples on new { entity.Id1, entity.Id2 }
                equals new { Id1 = pair.Item1, Id2 = pair.Item2 }
select entity

在 LINQ 到对象中,这将是完美的,但是,太糟糕了,EF 会抛出一个异常,例如

无法创建类型为"系统元组"2 的常量值 (...(在此上下文中仅支持基元类型或枚举类型。

这是一种相当笨拙的方式,告诉您它无法将此语句转换为 SQL,因为Tuples不是原始值的列表(如 intstring (。出于同样的原因,使用 Contains(或任何其他 LINQ 语句(的类似语句将失败。

解决方案 2:内存中

当然,我们可以将问题转换为简单的 LINQ 对象,如下所示:

from entity in db.Table.AsEnumerable() // fetch db.Table into memory first
join pair Tuples on new { entity.Id1, entity.Id2 }
             equals new { Id1 = pair.Item1, Id2 = pair.Item2 }
select entity

不用说,这不是一个好的解决方案。 db.Table可能包含数百万条记录。

解决方案 3:两个Contains语句(不正确(

因此,让我们为 EF 提供两个基元值列表,[1,2]用于Id1[2,3]用于Id2。我们不想使用 join,所以让我们使用 Contains

from entity in db.Table
where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2)
select entity

但现在结果还包含实体{1,3}!嗯,当然,这个实体完全匹配这两个谓词。但请记住,我们越来越近了。为了将数百万个实体拉入内存,我们现在只得到了其中的四个。

解决方案 4:一个包含计算值的Contains

解决方案 3 失败,因为两个单独的 Contains 语句不仅筛选其值的组合。如果我们先创建一个组合列表并尝试匹配这些组合怎么办?我们从解决方案 1 中知道此列表应包含基元值。例如:

var computed = ids1.Zip(ids2, (i1,i2) => i1 * i2); // [2,6]

和 LINQ 语句:

from entity in db.Table
where computed.Contains(entity.Id1 * entity.Id2)
select entity

这种方法存在一些问题。首先,您将看到这也返回实体{1,6}。组合函数 (a*b( 不会生成唯一标识数据库中的一对的值。现在我们可以创建一个字符串列表,例如 ["Id1=1,Id2=2","Id1=2,Id2=3]" 和 do

from entity in db.Table
where computed.Contains("Id1=" + entity.Id1 + "," + "Id2=" + entity.Id2)
select entity

(这将在 EF6 中工作,而不是在早期版本中(。

这变得非常混乱。但一个更重要的问题是,此解决方案不可优化优化,这意味着:它绕过了Id1Id2上本可以使用的任何数据库索引。这将表现得非常非常差。

解决方案 5:2 和 3 中的最优

因此,我能想到的最可行的解决方案是将内存中的Containsjoin组合在一起:首先执行解决方案3中的包含语句。请记住,它让我们非常接近我们想要的东西。然后,通过将结果联接为内存中列表来优化查询结果:

var rawSelection = from entity in db.Table
                   where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2)
                   select entity;
var refined = from entity in rawSelection.AsEnumerable()
              join pair in Tuples on new { entity.Id1, entity.Id2 }
                              equals new { Id1 = pair.Item1, Id2 = pair.Item2 }
              select entity;

它可能并不优雅,混乱,但到目前为止,它是我发现并应用于我自己的代码中的唯一可扩展的1 解决方案。

解决方案 6:使用 OR 子句生成查询

使用 Linqkit 等谓词生成器或替代项,可以生成一个查询,其中包含组合列表中每个元素的 OR 子句。对于非常短的列表来说,这可能是一个可行的选择。对于几百个元素,查询将开始执行得非常差。所以我不认为这是一个好的解决方案,除非你能100%确定总会有少量的元素。可以在此处找到此选项的一个详细说明。

解决方案 7:工会

还有一个使用 UNION 的解决方案,我稍后在这里发布。

<小时 />

1Contains 语句的可伸缩性而言:针对 SQL 后端的 LINQ 的可伸缩包含方法

Entity Framework Core with SQL Server 的解决方案

新增功能!可查询值EF6 Edition已经到来!

以下解决方案使用可查询值。这是我编写的一个库,主要用于解决 SQL Server 中由使用 Contains LINQ 方法组成本地值的查询引起的查询计划缓存污染问题。它还允许您以高性能的方式在查询中组合复杂类型的值,这将实现此问题中提出的内容。

首先,您需要安装和设置库,然后可以使用以下任何模式,这些模式将允许您使用组合键查询实体:

// Required to make the AsQueryableValues method available on the DbContext.
using BlazarTech.QueryableValues;
// Local data that will be used to query by the composite key
// of the fictitious OrderProduct table.
var values = new[]
{
    new { OrderId = 1, ProductId = 10 },
    new { OrderId = 2, ProductId = 20 },
    new { OrderId = 3, ProductId = 30 }
};
// Optional helper variable (needed by the second example due to CS0854)
var queryableValues = dbContext.AsQueryableValues(values);
// Example 1 - Using a Join (preferred).
var example1Results = dbContext
    .OrderProduct
    .Join(
        queryableValues,
        e => new { e.OrderId, e.ProductId },
        v => new { v.OrderId, v.ProductId },
        (e, v) => e
    )
    .ToList();
// Example 2 - Using Any (similar behavior as Contains).
var example2Results = dbContext
    .OrderProduct
    .Where(e => queryableValues
        .Where(v =>
            v.OrderId == e.OrderId &&
            v.ProductId == e.ProductId
        )
        .Any()
    )
    .ToList();

有用的链接

  • 努吉特包
  • GitHub 存储库
  • 基准

QueryableValues 在 MIT 许可证下分发。

您可以对每个复合主键使用 Union

var compositeKeys = new List<CK> 
{
    new CK { id1 = 1, id2 = 2 },
    new CK { id1 = 1, id2 = 3 },
    new CK { id1 = 2, id2 = 4 }
};
IQuerable<CK> query = null;
foreach(var ck in compositeKeys)
{
    var temp = context.Table.Where(x => x.id1 == ck.id1 && x.id2 == ck.id2);
    query = query == null ? temp : query.Union(temp);
}
var result = query.ToList();

你可以像这样用两个键创建一个字符串集合(我假设你的键是 int 类型(:

var id1id2Strings = listOfIds.Select(p => p.Id1+ "-" + p.Id2);

然后,您可以在数据库上使用"包含":

using (dbEntities context = new dbEntities())
            {
                var rec = await context.Table1.Where(entity => id1id2Strings .Contains(entity.Id1+ "-" + entity.Id2));
                return rec.ToList();
            }

您需要一组表示要查询的键的对象。

class Key
{
    int Id1 {get;set;}
    int Id2 {get;set;}

如果你有两个列表,你只是检查每个值是否出现在它们各自的列表中,那么你得到的是列表的笛卡尔乘积 - 这可能不是你想要的。相反,您需要查询所需的特定组合

List<Key> keys = // get keys;
context.Table.Where(q => keys.Any(k => k.Id1 == q.Id1 && k.Id2 == q.Id2)); 

我不完全确定这是实体框架的有效使用;您可能在将Key类型发送到数据库时遇到问题。如果发生这种情况,那么您可以发挥创意:

var composites = keys.Select(k => p1 * k.Id1 + p2 * k.Id2).ToList();
context.Table.Where(q => composites.Contains(p1 * q.Id1 + p2 * q.Id2)); 

你可以创建一个同构函数(素数对此很好(,类似于哈希码,你可以用它来比较一对值。只要乘法因子是互质的,这种模式将是同构的(一对一( - 即,只要正确选择素数,p1*Id1 + p2*Id2的结果将唯一标识Id1Id2的值。

但是你最终会遇到这样一种情况:你正在实现复杂的概念,并且必须有人支持这一点。最好编写一个采用有效键对象的存储过程。

遇到了这个问题,需要一个既不执行表扫描又提供完全匹配的解决方案。

这可以通过结合Gert Arnold的答案中的解决方案3和解决方案4来实现。

var firstIds = results.Select(r => r.FirstId);
var secondIds = results.Select(r => r.SecondId);
var compositeIds = results.Select(r => $"{r.FirstId}:{r.SecondId}");
var query = from e in dbContext.Table
 
            //first check the indexes to avoid a table scan
            where firstIds.Contains(e.FirstId) && secondIds.Contains(e.SecondId))
 
            //then compare the compositeId for an exact match
            //ToString() must be called unless using EF Core 5+
            where compositeIds.Contains(e.FirstId.ToString() + ":" + e.SecondId.ToString()))
            select e;
var entities = await query.ToListAsync();

对于 EF Core,我使用 EricEJ 的桶化 IN 方法的略微修改版本将复合键映射为元组。它对于小型数据集的性能非常好。

示例用法

List<(int Id, int Id2)> listOfIds = ...
context.Table.In(listOfIds, q => q.Id, q => q.Id2);

实现

public static IQueryable<TQuery> In<TKey1, TKey2, TQuery>(
            this IQueryable<TQuery> queryable,
            IEnumerable<(TKey1, TKey2)> values,
            Expression<Func<TQuery, TKey1>> key1Selector,
            Expression<Func<TQuery, TKey2>> key2Selector)
        {
            if (values is null)
            {
                throw new ArgumentNullException(nameof(values));
            }
            if (key1Selector is null)
            {
                throw new ArgumentNullException(nameof(key1Selector));
            }
            if (key2Selector is null)
            {
                throw new ArgumentNullException(nameof(key2Selector));
            }
            if (!values.Any())
            {
                return queryable.Take(0);
            }
            var distinctValues = Bucketize(values);
            if (distinctValues.Length > 1024)
            {
                throw new ArgumentException("Too many parameters for SQL Server, reduce the number of parameters", nameof(values));
            }
            var predicates = distinctValues
                .Select(v =>
                {
                    // Create an expression that captures the variable so EF can turn this into a parameterized SQL query
                    Expression<Func<TKey1>> value1AsExpression = () => v.Item1;
                    Expression<Func<TKey2>> value2AsExpression = () => v.Item2;
                    var firstEqual = Expression.Equal(key1Selector.Body, value1AsExpression.Body);
                    var visitor = new ReplaceParameterVisitor(key2Selector.Parameters[0], key1Selector.Parameters[0]);
                    var secondEqual = Expression.Equal(visitor.Visit(key2Selector.Body), value2AsExpression.Body);
                    return Expression.AndAlso(firstEqual, secondEqual);
                })
                .ToList();
            while (predicates.Count > 1)
            {
                predicates = PairWise(predicates).Select(p => Expression.OrElse(p.Item1, p.Item2)).ToList();
            }
            var body = predicates.Single();
            var clause = Expression.Lambda<Func<TQuery, bool>>(body, key1Selector.Parameters[0]);
            return queryable.Where(clause);
        }
        class ReplaceParameterVisitor : ExpressionVisitor
        {
            private ParameterExpression _oldParameter;
            private ParameterExpression _newParameter;
            public ReplaceParameterVisitor(ParameterExpression oldParameter, ParameterExpression newParameter)
            {
                _oldParameter = oldParameter;
                _newParameter = newParameter;
            }
            protected override Expression VisitParameter(ParameterExpression node)
            {
                if (ReferenceEquals(node, _oldParameter))
                    return _newParameter;
                return base.VisitParameter(node);
            }
        }
        /// <summary>
        /// Break a list of items tuples of pairs.
        /// </summary>
        private static IEnumerable<(T, T)> PairWise<T>(this IEnumerable<T> source)
        {
            var sourceEnumerator = source.GetEnumerator();
            while (sourceEnumerator.MoveNext())
            {
                var a = sourceEnumerator.Current;
                sourceEnumerator.MoveNext();
                var b = sourceEnumerator.Current;
                yield return (a, b);
            }
        }
        private static TKey[] Bucketize<TKey>(IEnumerable<TKey> values)
        {
            var distinctValueList = values.Distinct().ToList();
            // Calculate bucket size as 1,2,4,8,16,32,64,...
            var bucket = 1;
            while (distinctValueList.Count > bucket)
            {
                bucket *= 2;
            }
            // Fill all slots.
            var lastValue = distinctValueList.Last();
            for (var index = distinctValueList.Count; index < bucket; index++)
            {
                distinctValueList.Add(lastValue);
            }
            var distinctValues = distinctValueList.ToArray();
            return distinctValues;
        }

在没有通用解决方案的情况下,我认为需要考虑两件事:

  1. 避免使用多列主键(也会使单元测试更容易(。
  2. 但是,如果必须这样做,其中之一可能会减少查询结果大小为 O(n(,其中 n 是理想查询的大小结果。 从这里开始,上面的Gerd Arnold的解决方案5。

例如,导致我提出这个问题的问题是查询订单行,其中键是订单 ID + 订单行号 + 订单类型,而源的订单类型是隐式的。 也就是说,订单类型是一个常量,订单 ID 会将查询集减少到相关订单的订单行,并且每个订单通常有 5 个或更少的订单。

改写一下:如果你有一个组合键,则更改是其中一个键的重复项很少。 应用上面的解决方案 5。

我尝试了这个解决方案,它对我有用,输出查询是完美的,没有任何参数

using LinqKit; // nuget     
   var customField_Ids = customFields?.Select(t => new CustomFieldKey { Id = t.Id, TicketId = t.TicketId }).ToList();
    var uniqueIds1 = customField_Ids.Select(cf => cf.Id).Distinct().ToList();
    var uniqueIds2 = customField_Ids.Select(cf => cf.TicketId).Distinct().ToList();
    var predicate = PredicateBuilder.New<CustomFieldKey>(false); //LinqKit
    var lambdas = new List<Expression<Func<CustomFieldKey, bool>>>();
    foreach (var cfKey in customField_Ids)
    {
        var id = uniqueIds1.Where(uid => uid == cfKey.Id).Take(1).ToList();
        var ticketId = uniqueIds2.Where(uid => uid == cfKey.TicketId).Take(1).ToList();
        lambdas.Add(t => id.Contains(t.Id) && ticketId.Contains(t.TicketId));
    }
    predicate = AggregateExtensions.AggregateBalanced(lambdas.ToArray(), (expr1, expr2) =>
     {
         var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
         return Expression.Lambda<Func<CustomFieldKey, bool>>
               (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
     });

    var modifiedCustomField_Ids = repository.GetTable<CustomFieldLocal>()
         .Select(cf => new CustomFieldKey() { Id = cf.Id, TicketId = cf.TicketId }).Where(predicate).ToArray();

我最终为这个问题编写了一个依赖于System.Linq.Dynamic.Core的帮助程序;

它有很多代码,目前没有时间重构,但感谢输入/建议。

        public static IQueryable<TEntity> WhereIsOneOf<TEntity, TSource>(this IQueryable<TEntity> dbSet, 
            IEnumerable<TSource> source, 
            Expression<Func<TEntity, TSource,bool>> predicate) where TEntity : class
        {
            var (where, pDict) = GetEntityPredicate(predicate, source);
            return dbSet.Where(where, pDict);
            (string WhereStr, IDictionary<string, object> paramDict) GetEntityPredicate(Expression<Func<TEntity, TSource, bool>> func, IEnumerable<TSource> source)
            {
                var firstP = func.Parameters[0];
                var binaryExpressions = RecurseBinaryExpressions((BinaryExpression)func.Body);
                var i = 0;
                var paramDict = new Dictionary<string, object>();
                var res = new List<string>();
                foreach (var sourceItem in source)
                {
                    var innerRes = new List<string>();
                    foreach (var bExp in binaryExpressions)
                    {
                        var emp = ToEMemberPredicate(firstP, bExp);
                        var val = emp.GetKeyValue(sourceItem);
                        var pName = $"@{i++}";
                        paramDict.Add(pName, val);
                        var str = $"{emp.EntityMemberName} {emp.SQLOperator} {pName}";
                        innerRes.Add(str);
                    }
                    res.Add( "(" + string.Join(" and ", innerRes) + ")");
                }
                var sRes = string.Join(" || ", res);
                return (sRes, paramDict);
            }
            
            EMemberPredicate ToEMemberPredicate(ParameterExpression firstP, BinaryExpression bExp)
            {
                var lMember = (MemberExpression)bExp.Left;
                var rMember = (MemberExpression)bExp.Right;
                var entityMember = lMember.Expression == firstP ? lMember : rMember;
                var keyMember = entityMember == lMember ? rMember : lMember;
                return new EMemberPredicate(entityMember, keyMember, bExp.NodeType);
            }
            List<BinaryExpression> RecurseBinaryExpressions(BinaryExpression e, List<BinaryExpression> runningList = null)
            {
                if (runningList == null) runningList = new List<BinaryExpression>();
                if (e.Left is BinaryExpression lbe)
                {
                    var additions = RecurseBinaryExpressions(lbe);
                    runningList.AddRange(additions);
                }
                
                if (e.Right is BinaryExpression rbe)
                {
                    var additions = RecurseBinaryExpressions(rbe);
                    runningList.AddRange(additions);
                }
                if (e.Left is MemberExpression && e.Right is MemberExpression)
                {
                    runningList.Add(e);
                }
                return runningList;
            }
        }

帮助程序类:

    public class EMemberPredicate
    {
        public readonly MemberExpression EntityMember;
        public readonly MemberExpression KeyMember;
        public readonly PropertyInfo KeyMemberPropInfo;
        public readonly string EntityMemberName;
        public readonly string SQLOperator;
        public EMemberPredicate(MemberExpression entityMember, MemberExpression keyMember, ExpressionType eType)
        {
            EntityMember = entityMember;
            KeyMember = keyMember;
            KeyMemberPropInfo = (PropertyInfo)keyMember.Member;
            EntityMemberName = entityMember.Member.Name;
            SQLOperator = BinaryExpressionToMSSQLOperator(eType);
        }
        public object GetKeyValue(object o)
        {
            return KeyMemberPropInfo.GetValue(o, null);
        }
        private string BinaryExpressionToMSSQLOperator(ExpressionType eType)
        {
            switch (eType)
            {
                case ExpressionType.Equal:
                    return "==";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.NotEqual:
                    return "<>";
                default:
                    throw new ArgumentException($"{eType} is not a handled Expression Type.");
            }
        }
    }

像这样使用:

// This can be a Tuple or whatever..  If Tuple, then y below would be .Item1, etc.
// This data structure is up to you but is what I use.
[FromBody] List<CustomerAddressPk> cKeys
            var res = await dbCtx.CustomerAddress
                .WhereIsOneOf(cKeys, (x, y) => y.CustomerId == x.CustomerId 
                   && x.AddressId == y.AddressId)
                .ToListAsync();

希望这对其他人有所帮助。

在组合键的情况下,您可以使用另一个 idlist 并在代码中添加条件

context.Table.Where(q => listOfIds.Contains(q.Id) && listOfIds2.Contains(q.Id2));

或者您可以使用另一个技巧通过添加密钥来创建密钥列表

listofid.add(id+id1+......)
context.Table.Where(q => listOfIds.Contains(q.Id+q.id1+.......));

我在 EF Core 5.0.3 上使用 Postgres 提供程序尝试过这个。

context.Table
    .Select(entity => new
    {
        Entity = entity,
        CompositeKey = entity.Id1 + entity.Id2,
    })
    .Where(x => compositeKeys.Contains(x.CompositeKey))
    .Select(x => x.Entity);

这产生了如下 SQL:

SELECT *
FROM table AS t
WHERE t.Id1 + t.Id2 IN (@__compositeKeys_0)), 

警告

  • 这应该只用于Id1Id2的组合将始终产生唯一结果的情况(例如,它们都是UUID(
  • 这不能使用索引,尽管您可以将组合键保存到带有索引的数据库中