Entity Framework 5: How to Outer Join Table Valued Function

Question

I'm attempting to outer join a table with an inline table valued function in my LINQ query, but I get a query compilation error at runtime:

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

My linq statement looks like this:

     var testQuery = (from accountBase in ViewContext.AccountBases

                         join advisorConcatRaw in ViewContext.UFN_AccountAdvisorsConcatenated_Get()
                                on accountBase.AccountId equals advisorConcatRaw.AccountId into advisorConcatOuter
                         from advisorConcat in advisorConcatOuter.DefaultIfEmpty()

                         select new
                         {
                             accountBase.AccountId,
                             advisorConcat.Advisors
                         }).ToList();

The function definition is as follows:

CREATE FUNCTION dbo.UFN_AccountAdvisorsConcatenated_Get()
RETURNS TABLE
AS
RETURN
    SELECT  AP.AccountId,
            LEFT(AP.Advisors, LEN(AP.Advisors) - 1) AS Advisors
      FROM  (   SELECT  DISTINCT 
                        AP.AccountId,
                        (   SELECT  AP2.PropertyValue + ', '
                            FROM    dbo.AccountProperty AP2 WITH (NOLOCK)
                            WHERE   AP2.AccountId = AP.AccountId
                            AND     AP2.AccountPropertyTypeId = 1 -- Advisor 
                            FOR XML PATH('')) AS Advisors
                FROM    dbo.AccountProperty AP WITH (NOLOCK)) AP;

I can successfully perform the join directly in sql as follows:

 SELECT ab.accountid,
       advisorConcat.Advisors
FROM   accountbase ab
       LEFT OUTER JOIN dbo.Ufn_accountadvisorsconcatenated_get() advisorConcat
                    ON ab.accountid = advisorConcat.accountid

Does anyone have a working example of left outer joining an inline TVF to a table in LINQ to entities - or is this a known defect, etc? Many thanks.

Still no response on this from Microsoft - will this issue be fixed in the next version of EF? — Tony Prudente, Nov 01 '13 at 17:17
For EF6, check out https://github.com/moozzyk/CodeFirstFunctions and for EF Core it's now built-in with `DbFunctionAttribute`. — Dai, Jan 13 '21 at 13:23

score 1 · Answer 1 · answered Jun 15 '17 at 19:23

Entity Framework needs to know what the primary key columns of the TVF results are to do a left join. Basically you need to create a fake table which has same schema as your TVF results and update TVF in model browser to return the new created table type instead of default complex type. You could refer to this answer to get more details.

Entity Framework 5: How to Outer Join Table Valued Function

1 Answers1