c# - EntityFramework - 包含复合键的查询

Question

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

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

但是当 Table 有一个复合键时，你如何实现相同的功能呢？

Answer 1

这是一个令人讨厌的问题，我不知道任何优雅的解决方案。
假设您有这些组合键，而您只想选择带标记的组合键 (*)。

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

如何做到这一点是 Entity Framework 高兴的一种方式？让我们看看一些可能的解决方案，看看它们是否有任何好处。
解决方案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 to objects 这将是完美的，但是，太糟糕了，EF 会抛出一个异常

Unable to create a constant value of type 'System.Tuple`2 (...) Only primitive types or enumeration types are supported in this context.

这是一种相当笨拙的方式来告诉您它无法将此语句转换为 SQL，因为 Tuples不是原始值列表(如 int 或 string )。1。出于同样的原因，使用 Contains 的类似语句(或任何其他 LINQ 语句)将失败。
解决方案 2:内存中
当然，我们可以将问题转化为简单的 LINQ to objects，如下所示:

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]"并做

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

(这适用于 EF6，而不适用于早期版本)。
这变得非常困惑。但更重要的问题是，这个解决方案不是 sargable ，这意味着:它绕过 Id1 上的任何数据库索引和 Id2本来可以使用的。这将执行非常非常差。
解决方案 5:最好的 2 和 3
所以我能想到的唯一可行的解​​决方案是 Contains 的组合和一个 join在内存中:首先执行解决方案 3 中的 contains 语句。请记住，它使我们非常接近我们想要的。然后通过将结果作为内存列表加入来优化查询结果:

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;

它可能并不优雅、凌乱，但到目前为止，它是我发现并应用于我自己的代码中的唯一可扩展的解决方案。
解决方案 6:使用 OR 子句构建查询
使用像 Linqkit 或替代品这样的谓词构建器，您可以构建一个查询，其中包含组合列表中每个元素的 OR 子句。对于真正的短名单，这可能是一个可行的选择。如果有几百个元素，查询的性能就会很差。所以我不认为这是一个很好的解决方案，除非您可以 100% 确定总会有少量元素。可以找到此选项的详细说明 here .

1作为一个有趣的旁注，EF 确实会在您加入原始列表时创建一个 SQL 语句，就像这样

from entity in db.Table // db is a DbContext
join i in MyIntegers on entity.Id1 equals i
select entity

但是生成的 SQL 是荒谬的。一个真实的例子，其中 MyIntegers只包含 5(!) 个整数，如下所示:

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]

有 n-1 UNION s。当然，这根本不是可扩展的。
后期添加:
在通往 EF 6.1.3 版的道路上的某个地方，这已经得到了很大的改进。 UNION s 变得更简单，它们不再嵌套。以前查询会在本地序列中少于 50 个元素时放弃(SQL 异常:SQL 语句的某些部分嵌套太深。)非嵌套 UNION允许本地序列多达几千(!)个元素。尽管具有“许多”元素，但它仍然很慢。
2至于 Contains语句是可扩展的: Scalable Contains method for LINQ against a SQL backend