gpt4 book ai didi

c# - LINQ左外连接查询错误: OuterApply did not have the appropriate keys

转载 作者:可可西里 更新时间:2023-11-01 07:46:29 31 4
gpt4 key购买 nike

我正在使用 Entity Framework 作为我的 ORM 对两个 SQL 函数进行连接。执行查询时,我收到此错误消息:

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 中编写了相同的查询并返回了结果,所以我不确定问题出在哪里:

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 的响应: enter image description here

编辑这是在 LINQPad 中生成的 SQL 查询:

-- 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# 中,但再次遇到相同的错误。

最佳答案

问题是 Entity Framework 需要知道 TVF 结果的主键列是什么才能进行左连接,而默认生成的 EDMX 文件不包含该信息。您可以通过将 TVF 结果映射到实体(而不是默认映射到复杂类型)来添加键值信息。

同一查询在 LINQPad 中起作用的原因是 LINQPad 中用于连接到数据库的默认数据上下文驱动程序使用 LINQ to SQL(而非 Entity Framework )。但我能够让查询在 Entity Framework 中运行(最终)。

我建立了一个类似于表值函数的本地 SQL Server 数据库:

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 创建了一个测试项目,并使用 Visual Studio 2013 中的实体数据模型设计器从数据库生成了一个 EDMX 文件。使用此设置,我在尝试运行时遇到了同样的错误该查询:

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)
...

为左连接运行替代表达式会导致稍微不同的错误:

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

这是新异常的截断堆栈跟踪:

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)
...

Entity Framework是开源的,所以我们其实可以看看这个异常抛出的源码。此片段中的注释解释了问题所在 ( 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 的结果映射到具有主键的实体。然后 Entity Framework 将根据到该实体的映射知道 TVF 结果的键值,我们应该避免这些与丢失键相关的错误。

默认情况下,从数据库生成 EDMX 文件时,TVF 会映射到复杂类型。在 https://msdn.microsoft.com/en-us/library/vstudio/ee534438%28v=vs.100%29.aspx 上有关于如何更改它的说明。 .

在我的测试项目中,我添加了一个空表,其中的模式与 TVF 的输出相匹配,让模型设计器生成实体,然后我转到模型浏览器并更新函数导入以返回这些实体的集合实体(而不是自动生成的复杂类型)。进行这些更改后,相同的 LINQ 查询运行没有错误。

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:

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','')

关于c# - LINQ左外连接查询错误: OuterApply did not have the appropriate keys,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/27255181/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com