LINQ左外连接查询错误：OuterApply没有相应的键 [英] LINQ left outer join query error: OuterApply did not have the appropriate keys
问题描述
我正在使用Entity Framework作为我的ORM进行两个SQL函数的连接。当查询执行时，我收到以下错误消息：
I am doing a join on two SQL functions using Entity Framework as my ORM. When the query gets executed I get this error message:
The query attempted to call 'Outer Apply' over a nested query,
but 'OuterApply' did not have the appropriate keys
这是我的查询：
var ingredientAllergenData = (from ings in db.fnListIngredientsFromItem(productId, (short)itemType, productId)
join ingAllergens in db.fnListAllergensFromItems(productId.ToString(CultureInfo.InvariantCulture), (short)itemType, currentLang)
on ings.id equals ingAllergens.ingredientId into ingAllergensData
from allergens in ingAllergensData.DefaultIfEmpty()
where ings.table == "tblIng" || ings.table == ""
select new {ings, allergens}).ToList();
我在LINQPad中写了相同的查询，我收到了结果，所以我不知道问题是：
I wrote the same query in LINQPad and I got back results, so I'm not sure what the issue is:
var ingredientAllergenData = (from ings in fnListIngredientsFromItem(1232, 0, 1232)
join ingAllergens in fnListAllergensFromItems("1232", 0, 1)
on ings.Id equals ingAllergens.IngredientId into ingAllergensData
from allergens in ingAllergensData.DefaultIfEmpty()
where ings.Table == "tblIng" || ings.Table == ""
select new {ings, allergens}).ToList();
linqpad的响应：
The response from linqpad:
编辑
这是生成的SQL在LINQPad中查询：
EDIT This is the generated SQL query in LINQPad:
-- Region Parameters
DECLARE @p0 Int = 1232
DECLARE @p1 Int = 0
DECLARE @p2 Int = 1232
DECLARE @p3 VarChar(1000) = '1232'
DECLARE @p4 SmallInt = 0
DECLARE @p5 Int = 1
DECLARE @p6 VarChar(1000) = 'tblIng'
DECLARE @p7 VarChar(1000) = ''
-- EndRegion
SELECT [t0].[prodId] AS [ProdId], [t0].[id] AS [Id], [t0].[parent] AS [Parent], [t0].[name] AS [Name], [t0].[ing_gtin] AS [Ing_gtin], [t0].[ing_artsup] AS [Ing_artsup], [t0].[table] AS [Table], [t0].[quantity] AS [Quantity], [t2].[test], [t2].[prodId] AS [ProdId2], [t2].[ingredientId] AS [IngredientId], [t2].[allergenId] AS [AllergenId], [t2].[allergenName] AS [AllergenName], [t2].[level_of_containment] AS [Level_of_containment]
FROM [dbo].[fnListIngredientsFromItem](@p0, @p1, @p2) AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[prodId], [t1].[ingredientId], [t1].[allergenId], [t1].[allergenName], [t1].[level_of_containment]
FROM [dbo].[fnListAllergensFromItems](@p3, @p4, @p5) AS [t1]
) AS [t2] ON [t0].[id] = ([t2].[ingredientId])
WHERE ([t0].[table] = @p6) OR ([t0].[table] = @p7)
我也尝试将相同的数字硬编码到C＃中，并再次得到相同的错误。
I also tried hardcoding the same numbers into C# and got the same error again.
推荐答案
问题是实体框架需要知道TVF结果的主键列是做左连接，默认生成的EDMX文件不包含该信息。您可以通过将TVF结果映射到实体来添加键值信息（而不是映射到复杂类型的默认值）。
The problem is that Entity Framework needs to know what the primary key columns of the TVF results are to do a left join, and the default generated EDMX file does not contain that information. You can add the key value information by mapping the TVF results to an entity (instead of the default of mapping to a complex type).
同一查询的原理LINQPad是用于连接到LINQPad中的数据库的默认数据上下文驱动程序使用LINQ to SQL（而不是实体框架）。但是我能够将查询运行在实体框架（最终）中。
The reason the same query works in LINQPad is that the default Data Context driver for connecting to a database in LINQPad uses LINQ to SQL (not Entity Framework). But I was able to get the query to run in Entity Framework (eventually).
我设置了一个本地SQL Server数据库类似的表值函数：
I set up a local SQL Server database similar table-valued functions:
CREATE FUNCTION fnListIngredientsFromItem(@prodId int, @itemType1 smallint, @parent int)
RETURNS TABLE
AS
RETURN (
select prodId = 1232, id = 1827, parent = 1232, name = 'Ossenhaaspunten', ing_gtin = 3003210089821, ing_artsup=141020, [table] = 'tblIng', quantity = '2 K'
);
go
CREATE FUNCTION fnListAllergensFromItems(@prodIdString varchar(1000), @itemType2 smallint, @lang int)
RETURNS TABLE
AS
RETURN (
select prodId = '1232', ingredientId = 1827, allergenId = 11, allergenName = 'fish', level_of_containment = 2
union all
select prodId = '1232', ingredientId = 1827, allergenId = 16, allergenName = 'tree nuts', level_of_containment = 2
union all
select prodId = '1232', ingredientId = 1827, allergenId = 12, allergenName = 'crustacean and shellfish', level_of_containment = 2
);
go
我使用Entity Framework 6.1.2创建了一个测试项目，并生成了一个EDMX使用Visual Studio 2013中的实体数据模型设计器从数据库中创建文件。通过此设置，我可以在尝试运行该查询时获得相同的错误：
And I created a test project using Entity Framework 6.1.2 and generated an EDMX file from the database using the Entity Data Model Designer in Visual Studio 2013. With this setup, I was able to get the same error when trying to run that query:
System.NotSupportedException
HResult=-2146233067
Message=The query attempted to call 'OuterApply' over a nested query, but 'OuterApply' did not have the appropriate keys.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.Query.PlanCompiler.NestPullup.ApplyOpJoinOp(Op op, Node n)
at System.Data.Entity.Core.Query.PlanCompiler.NestPullup.VisitApplyOp(ApplyBaseOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.Visit(OuterApplyOp op, Node n)
...
运行左连接的替代表达式导致一个稍微不同的错误：
Running an alternate expression for a left join resulted in a slightly different error:
var ingredientAllergenData = (db.fnListIngredientsFromItem(1323, (short)0, 1)
.GroupJoin(db.fnListAllergensFromItems("1232", 0, 1),
ing => ing.id,
allergen => allergen.ingredientId,
(ing, allergen) => new { ing, allergen }
)
).ToList();
这是来自新例外的截断堆栈跟踪：
Here is a truncated stacktrace from the new exception:
System.NotSupportedException
HResult=-2146233067
Message=The nested query does not have the appropriate keys.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.Query.PlanCompiler.NestPullup.ConvertToSingleStreamNest(Node nestNode, Dictionary`2 varRefReplacementMap, VarList flattenedOutputVarList, SimpleColumnMap[]& parentKeyColumnMaps)
at System.Data.Entity.Core.Query.PlanCompiler.NestPullup.Visit(PhysicalProjectOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.PhysicalProjectOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
...
实体框架是开源，所以我们可以看看抛出这个异常的源代码。此代码段中的评论解释了问题所在（ https：// entityframework .codeplex.com / SourceControl / latest＃src / EntityFramework / Core / Query / PlanCompiler / NestPullup.cs ）：
Entity Framework is open source, so we can actually look at the source code where this exception is thrown. The comments in this snippet explains what the problem is (https://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework/Core/Query/PlanCompiler/NestPullup.cs):
// Make sure that the driving node has keys defined. Otherwise we're in
// trouble; we must be able to infer keys from the driving node.
var drivingNode = nestNode.Child0;
var drivingNodeKeys = Command.PullupKeys(drivingNode);
if (drivingNodeKeys.NoKeys)
{
// ALMINEEV: In this case we used to wrap drivingNode into a projection that would also project Edm.NewGuid() thus giving us a synthetic key.
// This solution did not work however due to a bug in SQL Server that allowed pulling non-deterministic functions above joins and applies, thus
// producing incorrect results. SQL Server bug was filed in "sqlbuvsts01\Sql Server" database as #725272.
// The only known path how we can get a keyless drivingNode is if
// - drivingNode is over a TVF call
// - TVF is declared as Collection(Row) is SSDL (the only form of TVF definitions at the moment)
// - TVF is not mapped to entities
// Note that if TVF is mapped to entities via function import mapping, and the user query is actually the call of the
// function import, we infer keys for the TVF from the c-space entity keys and their mappings.
throw new NotSupportedException(Strings.ADP_KeysRequiredForNesting);
}
这解释了导致该错误的路径，所以我们可以做的任何事情下车这个路径应该解决问题。假设我们必须这样做，就可以将表格值函数的结果加入，一个选项（也许唯一的选择？）是将TVF的结果映射到具有主键的实体。然后，实体框架将基于映射到该实体来了解TVF结果的关键值，并且我们应该避免与缺少的密钥相关的这些错误。
That explains the path that leads to that error, so anything we can do to get off that path should fix the problem. Assuming we have to do that left join on the results of a table-valued function, one option (maybe the only option?) is to map the results of the TVF to an entity that has a primary key. Then Entity Framework will know the key values of the TVF results based on the mapping to that entity, and we should avoid these errors related to missing keys.
默认情况下，生成来自数据库的EDMX文件，TVF映射到复杂类型。有关如何更改 https：// msdn的说明.microsoft.com / en-us / library / vstudio / ee534438％28v = vs.100％29.aspx 。
By default when generating an EDMX file from the database, a TVF is mapped to a complex type. There are instructions for how to change it at https://msdn.microsoft.com/en-us/library/vstudio/ee534438%28v=vs.100%29.aspx.
在我的测试项目中，我添加了一个空表，其中有一个与TVF的输出相匹配的模式，让模型设计者生成Entities，然后我去了模型浏览器，并更新了函数导入，以返回这些实体的集合（而不是自动生成的复合体类型）。进行这些更改后，同样的LINQ查询运行没有错误。
In my test project, I added an empty table with a schema that matched the output of the TVFs to get the model designer to generate Entities, then I went to the model browser and updated the function imports to return a collection of these entities (instead of the auto-generated complex types). After making these changes, that same LINQ query ran without errors.
var ingredientAllergenData = (from ings in db.fnListIngredientsFromItem(productId, (short)itemType, productId)
join ingAllergens in db.fnListAllergensFromItems(productId.ToString(CultureInfo.InvariantCulture), (short)itemType, currentLang)
on ings.id equals ingAllergens.ingredientId into ingAllergensData
from allergens in ingAllergensData.DefaultIfEmpty()
where ings.table == "tblIng" || ings.table == ""
select new {ings, allergens}).ToList();
这是查询给我的跟踪SQL：
Here is the trace SQL that the query gave me:
SELECT
1 AS [C1],
[Extent1].[prodId] AS [prodId],
[Extent1].[id] AS [id],
[Extent1].[parent] AS [parent],
[Extent1].[name] AS [name],
[Extent1].[ing_gtin] AS [ing_gtin],
[Extent1].[ing_artsup] AS [ing_artsup],
[Extent1].[table] AS [table],
[Extent1].[quantity] AS [quantity],
[Extent2].[prodId] AS [prodId1],
[Extent2].[ingredientId] AS [ingredientId],
[Extent2].[allergenId] AS [allergenId],
[Extent2].[allergenName] AS [allergenName],
[Extent2].[level_of_containment] AS [level_of_containment]
FROM [dbo].[fnListIngredientsFromItem](@prodId, @itemType1, @parent) AS [Extent1]
LEFT OUTER JOIN [dbo].[fnListAllergensFromItems](@prodIdString, @itemType2, @lang) AS [Extent2] ON ([Extent1].[id] = [Extent2].[ingredientId]) OR (([Extent1].[id] IS NULL) AND ([Extent2].[ingredientId] IS NULL))
WHERE [Extent1].[table] IN ('tblIng','')
这篇关于LINQ左外连接查询错误：OuterApply没有相应的键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
