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

Error processing SSI file

Answers

  1. Ruiz

    • 2018/5/29

    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','')
    
  2. Elias

    • 2019/4/21

    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 

  3. Robert

    • 2015/9/29

    @Aleks LINQ is trying to do an outer apply instead of a join. When you write an outer apply you normally take some of the output returned by function 1 and use it as input to function 2. Since you are passing values that do not come from function 1 LINQ complains that it doesn't know how to write the outer apply.

  4. Corey

    • 2018/5/12

    "The query attempted to call 'OuterApply' over a nested query, but 'OuterApply' did not have the appropriate keys." My linq statement looks like this:

  5. Peterson

    • 2021/8/24

    What is Left Join in Linq? The left join or left outer join is a join in which each data from the first data source is going to be returned irrespective of whether it has any correlated data present in the second data source or not. Please have a look at the following diagram which shows the graphical representation of Left Outer Join.

  6. Nico

    • 2019/4/28

    the query attempted call 'outer apply' on nested query, 'outerapply' did not have appropriate keys. this query:

  7. Harold

    • 2016/3/8

    The query attempted to call 'Outer Apply' over a nested query, but 'OuterApply' did not have the appropriate keys. Esta es mi consulta: 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

  8. Zaire

    • 2016/11/12

    LINQ left outer join query error: OuterApply did not have the appropriate keys. Virgil1972. Sep 25th 2021, 3:22 am. Never. You are currently not logged in, 

  9. Sinani

    • 2018/2/5

    SQL full outer join returns: all rows in the left table table_A. all rows in the right table table_B. and all matching rows in both tables. Some database management systems do not support SQL full outer join syntax e.g., MySQL. Because SQL full outer join returns a result set that is a combined result of both SQL left join and SQL right join.

  10. Brady

    • 2016/4/1

    Creating this query with LINQ to Entities is also relatively easy. an OUTER APPLY , which gave me some NULL logon names (not all Person 

  11. Brown

    • 2021/2/24

    In the previous examples, we explored the SQL Left Outer Join, and the SQL Right Outer Join with different examples. We can do a Union of the result of both SQL Left Outer Join and SQL Right Outer Join. It gives the output of SQL Full Outer Join. Execute the following query as an alternative to SQL Full Outer Join.

  12. Benedetti

    • 2015/3/20

    Not all LINQ operators have suitable translations on the server side. While the LINQ Join has outer and inner key selectors, 

  13. Foster

    • 2016/12/20

    In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Syntax: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name; Syntax diagram - FULL OUTER JOIN. Example: SQL FULL OUTER JOIN

  14. Galli

    • 2020/11/18

    Finally to left outer join with where clauses, i have detected you can still I need not solve later in LinQ Query which of using Join of table activity.

  15. Permeti

    • 2019/12/31

    RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID. ORDER BY Orders.OrderID; Try it Yourself ». Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders). Previous Next .

  16. Joaquin

    • 2016/7/14

    The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left 

  17. Bellini

    • 2020/8/19

    In this tip I will show you how I resolve a problem I have faced Below is a simple left outer join query to help us understand the 

  18. Simon

    • 2017/1/13

    So when INNER JOIN and LEFT/RIGHT OUTER JOIN are ANSI Standard and LINQ allowed you to compose a query once and have it work against any 

Comments are closed.

More Posts