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

UPDATE用EF core 2.1.0-preview2-Final测试过还是一样的问题

Answer 1

1. 升级到EF 2.0.2到EF核心2.1.0-预览2-最终
2. 使用FirstOrDefault（）而不是First（），
3. 不要使用let（"当使用let定义的子查询多次出现时存在已知问题"）请参阅https://github.com/aspnet/EntityFrameworkCore/issues/11677详细信息