c# - Linq2SQL生成的奇怪SQL

Question

我有一个 linq 查询，如下所示:(更大查询的一部分，但这说明了问题)

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
}

它生成以下 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?

我的问题是，为什么最后一个 ORDER BY 在那里？在我更大的查询中，这确实会损害性能，我无法弄清楚为什么需要它。

也欢迎任何以更好的方式编写此内容的提示。

Answer 1

在查询中，您在

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

这使得内部查询在内部 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]

但您正在“加入”2 个不同的集合，即空集和内部 block 集，为此，为了确保顺序，代码必须为结果集添加另一个排序方式 “join”的顺序，所以顺序在外部集合中的原因是自动代码生成，但因为集合已经排序，所以最后的 order by 不应该降低性能。