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
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.
source
share