LINQ оставил ошибку внешнего соединения: OuterApply не имел соответствующих ключей

Я делаю присоединение к двум функциям SQL, используя Entity Framework в качестве своего ORM. Когда запрос выполняется, я получаю следующее сообщение об ошибке:

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:

ИЗМЕНИТЬ Это сгенерированный SQL-запрос в 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)

Я также пытался жестко кодировать одни и те же номера в С# и снова получил ту же ошибку.

Ответ 1

Проблема заключается в том, что Entity Framework должна знать, какие столбцы первичного ключа в результатах TVF должны выполнять левое соединение, а файл EDMX, созданный по умолчанию, не содержит этой информации. Вы можете добавить информацию о значении ключа, сопоставляя результаты TVF с сущностью (вместо стандартного отображения для сложного типа).

Причина того же запроса в LINQPad заключается в том, что драйвер Data Context по умолчанию для подключения к базе данных в 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 и сгенерировал файл EDMX из базы данных с помощью конструктора модели данных Entity в Visual Studio 2013. С этой настройкой я смог получить ту же ошибку при попытке запустить этот запрос:

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

Вот усеченная stacktrace из нового исключения:

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, чтобы заставить модель конструктора генерировать Entities, затем я пошел в браузер модели и обновил импорт функций, чтобы вернуть коллекцию этих объектов (вместо автоматических генерируемых сложных типов). После внесения этих изменений этот же запрос 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','')