Strange SQL generated by Linq2SQL

Question

I have a linq query that looks as follows: (part of a bigger query but this demonstrates the problem)

from guarantee in tblGuarantees
from nextExptDev in
            (from gd in tblGuaranteeDevaluations
             where gd.fkGuaranteeId == guarantee.pkGuaranteeId &&
                   gd.Date == null
             orderby gd.ExpectedDate ascending
             select new
             {
                gd.Sum,
                gd.CurrencyId,
                gd.ExpectedDate
             }).Take(1).DefaultIfEmpty()
select new
{
    guarantee.pkGuaranteeId,
    nextExptDev.Sum,
    nextExptDev.CurrencyId,
    nextExptDev.ExpectedDate
}

It generates the following SQL:

SELECT [t0].[pkGuaranteeId],
       [t3].[Sum]          AS [Sum],
       [t3].[CurrencyId]   AS [CurrencyId],
       [t3].[ExpectedDate] AS [ExpectedDate2]
FROM   [dbo].[tblGuarantee] AS [t0]
       CROSS APPLY ((SELECT NULL AS [EMPTY]) AS [t1]
                    OUTER APPLY (SELECT TOP (1) [t2].[Sum],
                                                [t2].[CurrencyId],
                                                [t2].[ExpectedDate]
                                 FROM   [dbo].[tblGuaranteeDevaluation] AS [t2]
                                 WHERE  ( [t2].[fkGuaranteeId] = [t0].[pkGuaranteeId] )
                                        AND ( [t2].[Date] IS NULL )
                                 ORDER  BY [t2].[ExpectedDate]) AS [t3])
ORDER  BY [t3].[ExpectedDate] -- Why here?

My question is, why is that last ORDER BY there? In my bigger query that really hurts performance and I cant figure out why it is needed.

Also any hint on writing this in a better way is appreciated.

Answer 1

In the query you are doing a order by in the

from gd in tblGuaranteeDevaluations
         where gd.fkGuaranteeId == guarantee.pkGuaranteeId &&
               gd.Date == null
         orderby gd.ExpectedDate ascending

This made the inner query do the order by, in the inner block

SELECT TOP (1) [t2].[Sum], [t2].[CurrencyId], [t2].[ExpectedDate]
    FROM [dbo].[tblGuaranteeDevaluation] AS [t2]
    WHERE ([t2].[fkGuaranteeId] = [t0].[pkGuaranteeId]) AND ([t2].[Date] IS NULL)
    ORDER BY [t2].[ExpectedDate]

But you are "joining" 2 different sets, the null set and the inner block set, for that, to ensure the order, the code have to put another order by, for the result set of the "join", so that why the order is in the outer set, is automatic code generation, but because the set is already ordered, the last order by should not degrade performance.

Answer 2

What happens if you switch the DefaultIfEmpty() with the Take(1) call? What about replacing both with just a FirstOrDefault call? What about just using let nextExptDev = ... instead of from nextExptDev in ...?

Try one last thing for me... It seems that putting the order by within the projection is conveying to the rest of the query that you want the whole thing ordered by that. Instead, see if you can just select it from the ordered source. IE: from gd in tblGuaranteeDevaluations.OrderBy(t => t.ExpectedDate).