EntityFramework-包含组合键查询

给定ID列表，我可以通过以下方式查询所有相关行：

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

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

我喜欢EntityFramework，也喜欢复合键，但是我不喜欢它们。 EntityFramework的构建绝对考虑了简单的代理键。

#1 楼

这是一个令人讨厌的问题，我不知道有什么优雅的解决方法。
假设您具有这些键组合，并且只想选择标记的（*）。
这样做是使实体框架感到高兴的一种方式吗？让我们看看一些可能的解决方案，看看它们是否有用。
解决方案1：带有对的Join（或Contains）List<Tuple<int,int>>）并使用以下列表将数据库数据连接起来： >无法创建类型为'System.Tuple`2（...）的常量值，因此在此上下文中仅支持基本类型或枚举类型。它不能将该语句转换为SQL，因为Tuples不是原始值的列表（例如int或string）1。出于相同的原因，使用Contains的类似语句（或任何其他LINQ语句）也会失败。 />

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

不用说这不是一个好的解决方案。 db.Table可能包含数百万条记录。
解决方案3：两个Contains语句
所以让我们为EF提供两个原始值列表，[1,2]用于Id1，[2,3]用于Id2。我们不想使用join（请参阅旁注），因此我们使用Contains：

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

但是现在结果还包含实体{1,3}！好吧，当然，这个实体与两个谓词完全匹配。但是请记住，我们越来越近了。代替将数百万个实体拉入内存，我们现在只获得其中的四个。
解决方案4：一个带有计算值的Contains
解决方案3失败，因为两个单独的Contains语句不仅过滤了它们的值的组合。如果我们先创建一个组合列表并尝试匹配这些组合怎么办？从解决方案1中我们知道此列表应包含原始值。例如：

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

和LINQ语句：

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

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

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

（这在EF6中有效，在早期版本中不可用）。但是一个更重要的问题是，该解决方案不是可维护的，这意味着：它绕过了本可以用于Id1和Id2的所有数据库索引。
解决方案5：最好的2和3解决方案3.请记住，它使我们非常接近我们想要的东西。然后通过将结果作为内存列表加入来优化查询结果：
解决方案6：使用OR子句构建查询
使用诸如Linqkit或其他谓词的谓词构建器，可以构建一个查询，其中包含组合列表中每个元素的OR子句。对于真正的简短列表，这可能是一个可行的选择。有数百个元素，查询将开始表现很差。因此，除非您可以100％确保始终有少量元素，否则我认为这不是一个好的解决方案。有关此选项的详细说明，请参见此处。

1作为一个有趣的注释，当您加入原始列表时，EF确实会创建一条SQL语句，就像这样
一个真实的示例，其中Contains仅包含5（！）个整数：

 join

有n-1个MyIntegers s。当然，这根本无法扩展。
后来添加了：
在EF版本6.1.3的过程中，已经有了很大的改进。

SELECT 
    [Extent1].[CmpId] AS [CmpId], 
    [Extent1].[Name] AS [Name], 
    FROM  [dbo].[Company] AS [Extent1]
    INNER JOIN  (SELECT 
        [UnionAll3].[C1] AS [C1]
        FROM  (SELECT 
            [UnionAll2].[C1] AS [C1]
            FROM  (SELECT 
                [UnionAll1].[C1] AS [C1]
                FROM  (SELECT 
                    1 AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    2 AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
            UNION ALL
                SELECT 
                3 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
        UNION ALL
            SELECT 
            4 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
    UNION ALL
        SELECT 
        5 AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4] ON [Extent1].[CmpId] = [UnionAll4].[C1]

变得更简单，不再嵌套。以前，查询将放弃局部序列中少于50个元素（SQL异常：您的SQL语句的某些部分嵌套得太深。）非嵌套UNION允许局部序列最多包含数千个元素！。尽管带有“许多”元素，它仍然很慢。

这是我最终所做的（解决方案5），但这感觉像是一种糟糕的方法...

–斯特恩
2014年10月5日13:43

它是。问题的根源在于，我们必须处理一种绝望的过时语言规范（SQL），该规范从未提供过在一个语句中加入即席多维列表的方法（就像用IN语句处理简单列表一样）。有特定于RDBMS的变通方法或修补程序（Oracle确实是一个很好的解决方法），但EF可能不会在实施这些方法或修补程序上投入资金。

– Gert Arnold
2014年10月5日17:06

只是要清楚一点，如果正在运行的集合超过2100个元素（此处），. contains将引发异常。

–丹尼尔（Daniel）
15年4月8日在18:12

非常感谢您提供这个真有见地的答案。

–德克·波尔（Dirk Boer）
15年10月16日在21:07

解决方案7：编写一个存储过程，该过程对多个列进行联接。

–雅各布·霍尔布利克（Jacob Horbulyk）
16-2-28在22:34

#2 楼

您可以使用两个键来创建字符串集合（我假设您的键是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();
            }

正如Gert Arnord在他的评论中提到的那样：“该解决方案不是可靠的，这意味着：它绕过了本来可以使用的Id1和Id2上的任何数据库索引。”

–thefolenangel
5月25日21:04

#3 楼

您可以为每个组合主键使用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();

请问，这种方法的缺点是什么？在我看来，这是一个很好的解决方案，应该是公认的答案。谁能看到这个答案的缺点？

–山姆
19年7月25日在9:25

@Sam它将为每个CompositeKeys构建单独的sql查询，然后合并结果-这是此方法的弱点-性能

–lissajous
4月15日9:12

@ H.Wojtowicz，对于每个CompositeKeys，它都不会是单独的sql查询，它将是一个查询，其中包含多个子查询。

– Slava Utesinov
4月17日15:26

@SlavaUtesinov我不准确。我的意思是工会发生在SQL Server端。我的意思是查询是不必要的，只要所有必需条件都可以放在一个sql中，请参见-

–lissajous
4月18日10:32

#4 楼

如果是复合键，则可以使用另一个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+.......));

第一个查询是错误的（因为它假定两个值都是唯一的），第二个查询是错误的，但会导致全表扫描

–斯特恩
14-10-5在8:27

是的，我知道第一拳是错的，但是第二拳我会做的事给了我数据示例，你想要...

–user4093832
2014-10-5 13:07

抱歉，我的意思是第二个没错，但是它将导致全表扫描，这是不可接受的...

–斯特恩
2014年10月5日13:42

#5 楼

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

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

如果您有两个列表，只需检查每个值是否出现在各自的列表中，便可以正在获取列表的笛卡尔乘积-可能不是您想要的。相反，您需要查询所需的特定组合。

您可能在将Key类型发送到数据库时遇到问题。如果发生这种情况，那么您可以发挥创造力：

List<Key> keys = // get keys;

context.Table.Where(q => keys.Any(k => k.Id1 == q.Id1 && k.Id2 == q.Id2));

您可以创建同构函数（质数对此非常有用），例如哈希码，您可以使用比较这对值。只要乘法因子是互质的，该模式将是同构的（一对一）-即，只要正确选择质数，p1*Id1 + p2*Id2的结果将唯一地标识Id1和Id2的值。 >
但是最终您遇到的情况是您正在实施复杂的概念，并且有人将不得不对此予以支持。编写存储过程以使用有效的关键对象可能更好。

我将检查您的第一个解决方案，但对于第二个解决方案-虽然它可以工作，但会导致全表扫描，而不是对键使用直接查询

–斯特恩
2014年10月5日在8:28

@sternr完全正确。非常不愉快请注意我的最后一句话，您最好编写一个存储过程。

–柯克·布罗德赫斯特（Kirk Broadhurst）
2014年10月5日在21:26

#6 楼

在没有通用解决方案的情况下，我认为有两点要考虑：

避免使用多列主键（也将使单元测试更加容易）。您必须这样做，其中之一是将查询结果的大小减小到O（n），其中n是理想查询结果的大小。从这里开始，是上面Gerd Arnold的解决方案5。例如，导致我遇到此问题的问题是查询订单行，其中的关键是订单ID +订单行号+订单类型，以及源的订单类型为隐式。也就是说，订单类型是一个常量，订单ID会将查询集缩减为相关订单的订单行，通常每个订单中有5个或更少的订单行。

改写：如果您有一个复合键，更改之处在于其中之一只有很少的重复项。从上面应用解决方案5。

#7 楼

我尝试了此解决方案，它与我一起工作，并且输出查询非常完美，没有任何参数

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

编程黑洞网