LINQ left outer join query error: OuterApply did not have the appropriate keys

Question

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

This is my query:

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

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

The response from 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)

I also tried hardcoding the same numbers into C# and got the same error again.

Answer 1

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

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

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

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

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

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.

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.

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

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