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

但你怎么实现相同的功能，当表有一个复合键？

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)

如何做，这是一种方式，实体框架是幸福？让我们来看看一些可能的解决方案，看看他们有什么好处。

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.

最好的解决办法是创造你想要的对的列表，比如元组，（名单，其中元组LT; INT，INT＆GT;＆GT; ），并加入与此列表数据库中的数据：

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的对象，这将是完美的，但是，太​​糟糕了，英孚将引发像

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，因为元组不是原始值的列表（如 INT 或字符串）。 ¹ 。出于同样的原因，使用类似的语句包含（或任何其他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对象，像这样：

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 。我们不希望使用连接（见边注），所以我们使用包含：

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} ！嗯，当然，这个实体完全符合这两个predicates。但是，让我们记住，我们是越来越近了。在拉动数以百万计的实体到内存代替，我们现在只能拿到其中四个。

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失败，因为这两个独立的包含语句不仅过滤他们的价值观的组合的。如果我们创建的第一个组合列表，并尝试匹配这些组合？我们从溶液中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).

这是越来越pretty的凌乱。但是，一个更重要的问题是，这种解决方案并不 优化搜索 ，这意味着：它绕过任何在为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.

因此​​，唯一可行的解​​决办法，我能想到的是的组合中包含和加入内存：一是做包含语句，在溶液中3。请记住，这让我们非常接近我们想要的。然后，通过加入结果作为内存列表细化查询结果：

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;

这不是优雅的，杂乱的都是一样的，也许，但到目前为止，它是唯一可扩展的 ² 解决这个问题，我发现，在我自己的code应用。

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.

使用predicate建设者像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 that there will always be a small number of elements.

¹ 作为一个有趣的边注，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.

² 至于包含语句是可扩展性：的可伸缩包含方法LINQ对SQL后端

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

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