I'm trying to create a linq query that gives me a list with the number column from a master table with the count of detail records. My problem is that linq spits out a query without an outer apply which makes the query take 15 seconds. If I create the SQL myself using an outer apply the same query takes less then a second.
TekMas.Select(x => new {x.TekNr,Cnt = x.TekRev.Count})
This creates the following sql
SELECT
[Extent1].[TekMasID] AS [TekMasID],
[Extent1].[TekNr] AS [TekNr],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[TekRev] AS [Extent2]
WHERE [Extent1].[TekMasID] = [Extent2].[TekMasID]) AS [C1]
FROM [dbo].[TekMas] AS [Extent1]
I'm trying to create the following SQL using linq
SELECT TekMas.TekNr, RevCnt.Cnt
FROM TekMas
OUTER APPLY ( SELECT COUNT (TekRevID) AS Cnt
FROM TekRev
WHERE TekRev.TekMasID = TekMas.TekMasID) RevCnt;
I know that I can create an outer apply by using only the first detail record like this
TekMas.Select(x => new { x.TekNr, Cnt = x.TekRev.FirstOrDefault() })
.Select(x => new { x.TekNr, x.Cnt.TekRevID, x.Cnt.TekRevInf })
This linq create the following SQL result
SELECT
[Extent1].[TekMasID] AS [TekMasID],
[Extent1].[TekNr] AS [TekNr],
[Limit1].[TekRevID] AS [TekRevID],
[Limit1].[TekRevInf] AS [TekRevInf]
FROM [dbo].[TekMas] AS [Extent1]
OUTER APPLY (SELECT TOP (1)
[Extent2].[TekRevID] AS [TekRevID],
[Extent2].[TekRevInf] AS [TekRevInf]
FROM [dbo].[TekRev] AS [Extent2]
WHERE [Extent1].[TekMasID] = [Extent2].[TekMasID] ) AS [Limit1]
Is there a solution to force linq to create an outer apply when using count, just like it does on FirstOrDefault() in the last example
Thanks Stephen