c# - EF Core 2.0.2 为子查询生成多个查询

Question

我不确定这是否是错误，但我遇到了一些主要的性能问题。 EF Core 正在为一个简单的语句生成多个查询。

这是查询:

        var query = (from task in db.Tasks
                     let LastStatus = task.TaskStatus.Where(x => x.Deleted == false).OrderByDescending(x => x.CreatedDate).Select(x => x.Status.Name).First()
                     select new
                     {
                         task.Id,
                         LastStatus
                     }).ToList();

预期结果:

SELECT [t0].[Id], (
    SELECT TOP (1) [t2].[Name]
    FROM [TaskStatus] AS [t1]
    INNER JOIN [Status] AS [t2] ON [t2].[Id] = [t1].[StatusId]
    WHERE ([t1].[TaskId] = [t0].[Id]) AND (NOT ([t1].[Deleted] = 1))
    ORDER BY [t1].[CreatedDate] DESC
    ) AS [LastStatus]
FROM [Tasks] AS [t0]

但是 EF 正在生成:

SELECT TOP(1) [x.Status0].[Name]
FROM [TaskStatus] AS [x0]
INNER JOIN [Status] AS [x.Status0] ON [x0].[StatusId] = [x.Status0].[Id]
WHERE ([x0].[TaskId] = @_outer_Id) AND ([x0].[Deleted] = 0)
ORDER BY [x0].[CreatedDate] DESC
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [x.Status0].[Name]
FROM [TaskStatus] AS [x0]
INNER JOIN [Status] AS [x.Status0] ON [x0].[StatusId] = [x.Status0].[Id]
WHERE ([x0].[TaskId] = @_outer_Id) AND ([x0].[Deleted] = 0)
ORDER BY [x0].[CreatedDate] DESC

更新用EF core 2.1.0-preview2-final测试还是一样的问题

Answer 1

1. 升级到 EF 2.0.2 到 EF core 2.1.0-preview2-final
2. 使用 FirstOrDefault() 而不是 First()，
3. 不要使用“let”(“当使用 let 定义的子查询以后多次出现时存在已知问题”)参见 https://github.com/aspnet/EntityFrameworkCore/issues/11677详情