1

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

Zer0ne
  • 11
  • 2
  • Could you try using a left join in LINQ instead? I would hope the SQL optimizer would know that the sub-query could be converted to the `OUTER APPLY` but since it doesn't seem to, I think you would need to switch to join. – NetMage Oct 30 '19 at 20:34
  • @NetMage I tried using GroupJoin but still same result – Zer0ne Nov 05 '19 at 09:13

0 Answers0