Entity Framework 5: as a function of "Outer join table"

I am trying to perform an external join of a table with the built-in table evaluation function in my LINQ query, but at runtime I get a query compilation error:

"The request tried to call" OuterApply "on a nested request, but" OuterApply "did not have the corresponding keys.

My linq statement is as follows:

     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 complete the connection 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 a left outer join of an embedded TVF to a table in LINQ for entities - or is this a known bug, etc.? Many thanks.

+5
source share
1

Entity Framework , TVF . , TVF, TVF , , . , .

+1

All Articles