EntityFramework - 包含复合键的查询 [英] EntityFramework - contains query of composite key

问题描述

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

given a list of ids, I can query all relevant rows by:

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

但是当 Table 具有复合键时，您如何实现相同的功能?

But how do you achieve the same functionality when the Table has a composite key?

推荐答案

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

This is a nasty problem for which I don't know any elegant solution.

假设您有这些组合键，而您只想选择带标记的 (*).

Suppose you have these key combinations, and you only want to select the marked ones (*).

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

如何做到这一点是Entity Framework 高兴的方式?让我们看看一些可能的解决方案，看看它们是否有任何好处.

How to do this is a way that Entity Framework is happy? Let's look at some possible solutions and see if they're any good.

最好的解决方案是创建您想要的对的列表，例如元组，(List>) 并将数据库数据与此列表连接:

The best solution would be to create a list of the pairs you want, for instance Tuples, (List<Tuple<int,int>>) and join the database data with this list:

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 会抛出一个异常

In LINQ to objects this would be perfect, but, too bad, EF will throw an exception like

无法创建类型为 'System.Tuple`2 (...) 的常量值在此上下文中仅支持原始类型或枚举类型.

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).¹.出于同样的原因，使用 Contains(或任何其他 LINQ 语句)的类似语句将失败.

which is a rather clumsy way to tell you that it can't translate this statement into SQL, because Tuples is not a list of primitive values (like int or string).¹. For the same reason a similar statement using Contains (or any other LINQ statement) would fail.

当然，我们可以将问题转化为简单的 LINQ to objects，如下所示:

Of course we could turn the problem into simple LINQ to objects like so:

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 可以包含数百万条记录.

Needless to say that this is not a good solution. db.Table could contain millions of records.

所以让我们为 EF 提供两个原始值列表，[1,2] 用于 Id1，[2,3] 用于 >Id2.我们不想使用 join(见附注)，所以让我们使用 Contains:

So let's offer EF two lists of primitive values, [1,2] for Id1 and [2,3] for Id2. We don't want to use join (see side note), so let's use Contains:

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

但现在结果还包含实体{1,3}！嗯，当然，这个实体完美地匹配了两个谓词.但让我们记住，我们越来越近了.我们现在没有将数百万个实体拉入内存，而是只得到其中的四个.

But now the results also contains entity {1,3}! Well, of course, this entity perfectly matches the two predicates. But let's keep in mind that we're getting closer. In stead of pulling millions of entities into memory, we now only get four of them.

解决方案 3 失败了，因为两个单独的 Contains 语句不仅过滤了它们的值的组合.如果我们先创建一个组合列表并尝试匹配这些组合会怎样?我们从解决方案 1 中知道这个列表应该包含原始值.例如:

Solution 3 failed because the two separate Contains statements don't only filter the combinations of their values. What if we create a list of combinations first and try to match these combinations? We know from solution 1 that this list should contain primitive values. For instance:

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]" 并执行

There are some problems with this approach. First, you'll see that this also returns entity {1,6}. The combination function (a*b) does not produce values that uniquely identify a pair in the database. Now we could create a list of strings like ["Id1=1,Id2=2","Id1=2,Id2=3]" and do

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

(这适用于 EF6，不适用于早期版本).

(This would work in EF6, not in earlier versions).

这变得非常混乱.但是一个更重要的问题是这个解决方案不是sargable，这意味着:它绕过了任何Id1 和 Id2 上的数据库索引，否则可能会被使用.这会非常糟糕.

This is getting pretty messy. But a more important problem is that this solution is not sargable, which means: it bypasses any database indexes on Id1 and Id2 that could have been used otherwise. This will perform very very poorly.

所以我能想到的唯一可行的解​​决方案是在内存中结合 Contains 和 join:首先执行解决方案 3 中的 contains 语句.记住，它让我们非常接近我们想要的.然后通过将结果作为内存列表加入来细化查询结果:

So the only viable solution I can think of is a combination of Contains and a join in memory: First do the contains statement as in solution 3. Remember, it got us very close to what we wanted. Then refine the query result by joining the result as an in-memory list:

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;

它可能并不优雅、凌乱，但到目前为止，它是我发现并应用于我自己的代码中的唯一可扩展²解决方案.

It's not elegant, messy all the same maybe, but so far it's the only scalable² solution to this problem I found, and applied in my own code.

使用像 Linqkit 或替代品这样的谓词构建器，您可以构建一个查询，该查询包含组合列表中每个元素的 OR 子句.对于非常短的列表，这可能是一个可行的选择.如果有几百个元素，查询的性能就会很差.所以我不认为这是一个很好的解决方案，除非您可以 100% 确定总会有少量元素.可以在此处找到此选项的详细说明.

Using a Predicate builder like Linqkit or alternatives, you can build a query that contains an OR clause for each element in the list of combinations. This could be a viable option for really short lists. With a couple of hundreds of elements, the query will start performing very poorly. So I don't consider this a good solution unless you can be 100% sure that there will always be a small number of elements. One elaboration of this option can be found here.

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

¹As a funny side note, EF does create a SQL statement when you join a primitive list, like so

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

但是生成的 SQL 非常荒谬.MyIntegers 仅包含 5(!) 个整数的真实示例如下所示:

But the generated SQL is, well, absurd. A real-life example where MyIntegers contains only 5(!) integers looks like this:

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.当然，这根本无法扩展.

There are n-1 UNIONs. Of course that's not scalable at all.

后期补充:
在通往 EF 6.1.3 版的道路上的某个地方，这已经得到了很大的改进.UNION 变得更简单，它们不再嵌套.以前查询会在本地序列中少于 50 个元素时放弃(SQL 异常:SQL 语句的某些部分嵌套太深.)非嵌套 UNION允许本地序列多达几千(！)个元素.尽管很多"仍然很慢元素.

Later addition:
Somewhere along the road to EF version 6.1.3 this has been greatly improved. The UNIONs have become simpler and they are no longer nested. Previously the query would give up with less than 50 elements in the local sequence (SQL exception: Some part of your SQL statement is nested too deeply.) The non-nested UNION allow local sequences up to a couple of thousands(!) of elements. It's still slow though with "many" elements.

²只要 Contains 语句是可扩展的:Scalable Contains 方法用于针对 SQL 后端的 LINQ

²As far as the Contains statement is scalable: Scalable Contains method for LINQ against a SQL backend

这篇关于EntityFramework - 包含复合键的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！