Ich führe einen Join für zwei SQL-Funktionen mit Entity Framework als ORM durch. Wenn die Abfrage ausgeführt wird, erhalte ich folgende Fehlermeldung:

The query attempted to call 'Outer Apply' over a nested query,
but 'OuterApply' did not have the appropriate keys

Das ist meine Frage:

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

Ich habe die gleiche Abfrage in LINQPad geschrieben und habe die Ergebnisse zurückerhalten. Daher bin ich mir nicht sicher, was das Problem ist:

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

Die Antwort von linqpad:

BEARBEITE Dies ist die generierte SQL-Abfrage 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)

Ich habe auch versucht, die gleichen Zahlen in C # festzukodieren, und habe erneut den gleichen Fehler erhalten.