Linq to entity按SQL排序

Question

我正在测试使用EF和Linq to Entities来尝试提高我的应用程序性能。

我注意到了一些奇怪的东西（对我来说），我无法解释，也无法确定是否会产生可观的开销。

这是我的Linq：

var result = from n in query
        orderby n.PersonId
        select new
        {
            id = n.Id,
            appointmentId = n.AppointmentId,
            message = n.Message,
            wasRead = n.Read,
            canDismiss = (n.Appointment.Status != AppointmentStatus.Waiting),
            date = n.IssueDateUtc
        };

这是生成的SQL语句：

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[AppointmentId] AS [AppointmentId], 
    [Project1].[Message] AS [Message], 
    [Project1].[Read] AS [Read], 
    [Project1].[C1] AS [C1], 
    [Project1].[IssueDateUtc] AS [IssueDateUtc]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Read] AS [Read], 
        [Extent1].[Message] AS [Message], 
        [Extent1].[IssueDateUtc] AS [IssueDateUtc], 
        [Extent1].[AppointmentId] AS [AppointmentId], 
        [Extent1].[PersonId] AS [PersonId], 
        CASE WHEN ( NOT ((1 = [Extent2].[Status]) AND ([Extent2].[Status] IS NOT NULL))) THEN cast(1 as bit) WHEN (1 = [Extent2].[Status]) THEN cast(0 as bit) END AS [C1]
        FROM  [dbo].[Notification] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Appointment] AS [Extent2] ON [Extent1].[AppointmentId] = [Extent2].[Id]
        WHERE [Extent1].[PersonId] = @p__linq__0
    )  AS [Project1]
    **ORDER BY [Project1].[PersonId] ASC**

我不明白为什么需要将结果在另一个投影(Project1)中进行分组，而以下操作似乎可以正常工作：

SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Read] AS [Read], 
        [Extent1].[Message] AS [Message], 
        [Extent1].[IssueDateUtc] AS [IssueDateUtc], 
        [Extent1].[AppointmentId] AS [AppointmentId], 
        [Extent1].[PersonId] AS [PersonId], 
        CASE WHEN ( NOT ((1 = [Extent2].[Status]) AND ([Extent2].[Status] IS NOT NULL))) THEN cast(1 as bit) WHEN (1 = [Extent2].[Status]) THEN cast(0 as bit) END AS [C1]
        FROM  [dbo].[Notification] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Appointment] AS [Extent2] ON [Extent1].[AppointmentId] = [Extent2].[Id]
        WHERE [Extent1].[PersonId] = @p__linq__0
        **ORDER BY [Extent1].[PersonId] ASC**

我发现EF和LINQ生成的SQL存在相当多可疑代码，我开始怀疑是否直接编写原始SQL更好。问题是：生成的SQL中的额外代码是否值得担心？为什么需要那个投影？如评论中所述，可能是由于后续查询而导致的冗长。我重新编写了一条LINQ，只使用一个查询对象，结果仍然相同。

dbSet.Where(n => n.PersonId == id).Select(n => new
            {
                Id = n.Id,
                AppointmentId = n.AppointmentId,
                Message = n.Message,
                Read = n.Read,
                CanBeDismissed = (n.Appointment.Status != AppointmentStatus.Waiting),
                IssueDate = n.IssueDateUtc
            }).OrderBy(n => n.Id).ToList();

执行计划（对于两个sqls相同）

编辑2

从简单的计数中得到了这个查询。

dbSet.Count(x => x.Id == 1 && x.Read == false);

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Notification] AS [Extent1]
    WHERE ([Extent1].[PersonId] = 19) AND (0 = [Extent1].[Read])
)  AS [GroupBy1]

期望结果：

SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Notification] AS [Extent1]
    WHERE ([Extent1].[PersonId] = 19) AND (0 = [Extent1].[Read])

我无法理解所有这些包装器从哪里来以及为什么会出现。

- victor

---

我猜 query 是另一个 LINQ-to-SQL 查询，这就是你在投影中嵌套的内容。这正是你在使用 LINQ 做的事情。如果你想摆脱它，只需将 OrderBy 和 Select 添加到 query 中即可。但如果使用后续的 LINQ 查询使你的代码更清晰，那就不要担心... 投影几乎没有额外的开销。如有疑问，请比较执行计划。但如果你看到性能问题，那肯定不是由于该项目引起的。 - Bernhard Koenig

不用担心投影，这并不是 SQL 执行效率低的问题，只是有点啰嗦...尝试在 SQL Server Management Studio 中执行这两个查询，并激活执行计划和统计信息...你会发现结果几乎相同。至于如何摆脱投影，如果没有其他人介入，我明天会给你一个示例。但请告诉我 dbSet.Where() 中的 where 到底是做什么的。 - Bernhard Koenig

我在代码中添加了 where（n => n.PersonId == id）。谢谢。 - victor

匿名类型不是问题……问题在于在选择结果后执行排序。尝试将排序移至选择之前。 - Bernhard Koenig

查询有什么问题吗？OrderBy 最终都会由 SQL Server 完成。查询中需要更长时间的是索引扫描和索引查找，与“嵌套”选择无关。您是否比较了两个 SQL 语句的查询计划？ - Gabriel GM

Answer 1

我在我的电脑上组织了一个小的示例项目。在你的第一个示例中，实际上导致投影的原因是你对CanBeDismissed字段进行条件计算，从而导致SQL中的CASE WHEN。如果你去掉这个，Entity Framework就不会进行额外的投影。

所以通过条件检查：

db.Notifications
    .Where(n => n.AppointmentId == 1)
    .OrderBy(n => n.Id)
    .Select(n => new
    {
        Id = n.Id,
        Message = n.Message,
        HasMessage = n.Message != null
    }).ToList();

生成的SQL查询语句为：

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Message] AS [Message], 
    [Project1].[C1] AS [C1]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Message] AS [Message], 
        CASE WHEN ([Extent1].[Message] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
        FROM [dbo].[Notifications] AS [Extent1]
        WHERE 1 = [Extent1].[AppointmentId]
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC

以下是执行计划结果，以备日后参考：

如果不包含它：

db.Notifications
    .Where(n => n.AppointmentId == 1)
    .OrderBy(n => n.Id)
    .Select(n => new
    {
        Id = n.Id,
        Message = n.Message
    }).ToList();

EF 不进行任何投影操作：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Message] AS [Message]
    FROM [dbo].[Notifications] AS [Extent1]
    WHERE 1 = [Extent1].[AppointmentId]
    ORDER BY [Extent1].[Id] ASC

所以，这就是原因。对于您的count 示例同样适用：如果发生了任何分组操作，EF将添加一个额外的投影，使查询更加冗长。但重要的是，正如在您的问题评论中讨论的那样，它不会影响性能，没有必要担心这个额外的投影。

让我通过现在添加以下查询的执行计划来证明这一点，在这个查询中，我刚刚从第一个查询中删除了投影，并将orderby移动到了内部查询中：

SELECT
[Extent1].[Id] AS [Id], 
[Extent1].[Message] AS [Message], 
CASE WHEN ([Extent1].[Message] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
FROM [dbo].[Notifications] AS [Extent1]
WHERE 1 = [Extent1].[AppointmentId]
ORDER BY [Extent1].[Id] ASC

完全相同 - 没有添加任何额外任务，并且成本分布保持不变。 SQL查询优化器将很好地优化这些投影。

因此，不要担心投影 - 它们不会伤害您，尽管我同意它们看起来有时过于冗长。但是有两件事可能会帮助您:

性能问题：

首先，如果您的查询遇到性能问题，请查看为什么在您发布的执行计划中发生了Clustered Index Scan。这并不总是一些索引问题的迹象，但往往如此。您的问题可能在此处根源。

摆脱不必要的投影：

如果您仍然想在所有（或至少更多）情况下摆脱这些投影，则存在Entity Framework Core 1.0 - 它实际上比EF 6产生了更好的SQL。考虑迁移到它可能值得一试，但请注意，它没有EF 6提供的所有功能，因此如果您使用EF Core 1.0不提供的功能，则可能不是一个选项。但它将与完整的.NET Framework 4.x一起使用！

这是一个示例，当我执行我的答案的第一个LINQ语句时EF Core 1.0会产生什么：

SELECT [n].[Id], [n].[Message], CASE
    WHEN [n].[Message] IS NULL
    THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
END
FROM [Notifications] AS [n]
WHERE ([n].[Id] = 1) AND ([n].[Id] = 1)
ORDER BY [n].[Id]

- Bernhard Koenig

---

非常好，谢谢。我认为索引扫描发生是因为必须检查每一行以查看其fk列是否为X。在解决了这个条件之后，您可以在我发布的执行计划中看到，似乎数据库进行了索引搜索以获取具有id X的行以执行连接。我认为这是正确的，但不确定。尽管如此，在某些情况下，EF认为需要添加额外的投影仍然很奇怪。 - victor