由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

这使得内部查询通过在内部块中执行顺序

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]

但您正在"连接"两个不同的集合，即空集合和内部块集合，为此，为了确保顺序，代码必须通过放置另一个顺序，对于"连接"的结果集，因此顺序在外部集合中的原因是自动生成代码，但由于集合已经排序，的最后一个顺序不应降低性能。

Answer 2

如果用Take(1)调用切换DefaultIfEmpty()会发生什么？用一个FirstOrDefault调用来替换两者怎么样？只使用let nextExptDev = ...而不是from nextExptDev in ...怎么样？

我试着做最后一件事…似乎把order by放在投影中是在向查询的其余部分传达你希望整个事情都按它排序。相反，看看你是否可以从有序的来源中选择它。即：from gd in tblGuaranteeDevaluations.OrderBy(t => t.ExpectedDate)。

Answer 3

在您的查询中，您正在执行以下操作：

CCD_ 9，并反映在生成的SQL中。