EntityFramework - 包含复合键的查询 [英] EntityFramework - contains query of composite key
问题描述
context.Table.Where（q => ; listOfIds.Contains（q.Id））;
但是，当表具有复合键时，您如何实现相同的功能？
这是一个讨厌的问题，我不知道任何优雅的解决方案。
假设你有这些组合键，你只想选择标记的（*）。
Id1 Id2
--- ---
1 2 *
1 3
1 6
2 2 *
2 3 *
...（更多）
如何做到这一点，实体框架是开心的？我们来看看一些可能的解决方案，看看它们是否有好处。
解决方案1：加入（或包含）配对
最好的解决方案是创建一个所需对的列表，例如元组， （列表< Tuple< int，int>> ）并使用此列表加入数据库数据：
来自db.Table中的实体// db是新的{entity.Id1，entity.Id2}的元组中的一个DbContext
连接对
等于新的{Id1 = pair。 Item1，Id2 = pair.Item2}
选择实体
在LINQ对象中，这将是完美，但是太糟糕了，EF会抛出一个异常，如
无法创建类型为System.Tuple ...）在这种情况下，只支持原始类型或枚举类型。
这是一个相当笨拙的方式告诉你无法将此声明翻译成SQL，因为元组不是原始值的列表（如 int 或 string ）。 1 。出于同样的原因，使用包含（或任何其他LINQ语句）的类似语句将失败。
解决方案2：内存中
当然，我们可以将问题变成简单的LINQ到像这样的对象：
来自db.Table.AsEnumerable（）中的实体//将db.Table提取到内存首先
加入对新的{entity.Id1，entity.Id2}上的元组
等于新的{Id1 = pair.Item1，Id2 = pair.Item2}
选择实体
说这不是一个很好的解决方案。 db.Table 可能包含数百万条记录。
解决方案3：两个包含语句
语句所以让我们为EF提供两个原始值列表， [1,2] Id1 和 [2,3] 为 Id2 。我们不想使用加入（见附注），所以让我们使用包含：
来自db.Table
中的实体的其中ids1.Contains（entity.Id1）&&& ids2.Contains（entity.Id2）
选择实体
但现在结果还包含实体 {1,3} ！那么当然这个实体完全匹配了两个谓词。但是让我们记住，我们越来越近了。取而代之的是将数百万个实体吸引到记忆体中，我们现在只得到四个。
解决方案4：一个包含与计算值
解决方案3失败，因为两个单独的包含语句不仅会过滤组合的值。如果我们先创建一个组合列表并尝试匹配这些组合呢？我们从解决方案1知道这个列表应该包含原始值。例如：
var computed = ids1.Zip（ids2，（i1，i2）=> i1 * i2）; // [2,6]
和LINQ语句：
来自db.Table中的实体
其中calculate.Contains（entity.Id1 * entity.Id2）
选择实体
这种方法有一些问题。首先，你会看到这也返回实体 {1,6} 。组合函数（a * b）不产生唯一标识数据库中的对的值。现在我们可以创建一个如 [Id1 = 1，Id2 = 2，Id1 = 2，Id2 = 3]和
来自db.Table
中的实体，其中calculate.Contains（Id1 =+ entity.Id1 +，+Id2 = + entity.Id2）
选择实体
（这可以在EF6中工作，早期版本）。
这变得非常混乱。但更重要的一个问题是，该解决方案不是 sargable ，这意味着：它绕过任何 Id1 和 Id2 之间的数据库索引，否则可能已被使用。
解决方案5：最好的2和3
所以唯一可行的解决方案我可以想到的是一个组合的在内存中包含和 join ：首先在解决方案3中进行contains语句记住，它让我们非常接近我们想要的。然后通过将结果作为内存列表加入来优化查询结果：
var rawSelection =来自db.Table $中的实体b $ b其中ids1.Contains（entity.Id1）&& ids2.Contains（entity.Id2）
select entity;
var refine =来自rawSelection.AsEnumerable（）中的实体。
新的{entity.Id1，entity.Id2}中的元组中的连接对
等于新{Id1 = pair.Item1 ，Id2 = pair.Item2}
select entity;
它不是优雅，凌乱也可能，但到目前为止，它是唯一可扩展的 2
解决方案6：使用OR子句构建查询
使用像Linqkit这样的谓词构建器或替代方法，您可以构建一个包含组合列表中每个元素的OR子句的查询。这可能是真正简短的列表的可行选项。有几百个元素，查询将开始执行得很差。所以我不认为这是一个很好的解决方案，除非你可以100％肯定总是有少量的元素。 此处可以找到此选项的一个细节。
1 作为一个有趣的附注，当您加入原始列表时，EF 会创建一个SQL语句，如此
从$ db2中实体// db是一个DbContext
加入我在MyIntegers中的entity.Id1等于i
选择实体
但是生成的SQL是荒谬的。一个现实生活中的例子，其中 MyIntegers 只包含5（！）个整数，如下所示：
SELECT
[Extent1]。[CmpId] AS [CmpId]，
[Extent1]。[Name] AS [Name]，
FROM [dbo]。[公司] 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 变得更简单，不再嵌套。以前，查询将放弃本地序列中少于50个元素（SQL异常：您的SQL语句的某些部分嵌套太深。）非嵌套的 UNION 允许本地序列高达几千（！）个元素。
2 至于包含语句是可扩展的：针对SQL后端的LINQ的可扩展包含方法
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.
Solution 1: Join (or Contains) with pairs
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
In LINQ to objects this would be perfect, but, too bad, EF will throw an exception like
Unable to create a constant value of type 'System.Tuple`2 (...) Only primitive types or enumeration types are supported in this context.
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).1. For the same reason a similar statement using Contains (or any other LINQ statement) would fail.
Solution 2: In-memory
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
Needless to say that this is not a good solution. db.Table could contain millions of records.
Solution 3: Two Contains statements
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
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.
Solution 4: One Contains with computed values
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]
and the LINQ statement:
from entity in db.Table
where computed.Contains(entity.Id1 * entity.Id2)
select entity
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
(This would work in EF6, not in earlier versions).
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.
Solution 5: Best of 2 and 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;
It's not elegant, messy all the same maybe, but so far it's the only scalable2 solution to this problem I found, and applied in my own code.
Solution 6: Build a query with OR clauses
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.
1As 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
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]
There are n-1 UNIONs. Of course that's not scalable at all.
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.
2As far as the Contains statement is scalable: Scalable Contains method for LINQ against a SQL backend
这篇关于EntityFramework - 包含复合键的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
