我有一个带有EF Core 2.2代码优先DB的ASP.NET Core 2.2项目。我有以下实体：
建筑，基本上是其他一些重要数据的地址。
楼层，其中包含楼层号。一栋建筑物可以有多个楼层。地板必须精确地位于其所在的建筑物中。
房间，里面有一个数字。一个楼层可以有多个房间。一间房间必须恰好有一层。
工作组，其中包含该组中有多少名员工，该组是否仍处于活动状态以及该组何时开始运营（可能在将来）。
RoomOccupancy，它是一个工作组和一个房间之间的联接表，并显示该工作组在/曾经/将在哪个房间中。
我需要一个包含建筑物名称，建筑物的楼层数，建筑物的房间数（不是楼层）以及当前在建筑物中工作的人数的列表。
目前，我可以获取所有数据，但是转换后的SQL并不是最佳选择，并且需要多次访问数据库。对于该问题，我能够手动编写一个SQL select语句（带有内部select），所以我知道使用一个查询就可以做到这一点。
dbContext.Buildings.Select(x=> new BuildingDatableElementDTO(){
BuildingId = b.Id,
Name = b.Name,
FloorCount = b.Floors.Count(),
//this is the part where problems start,
//this translates to multiple SQL statements
RoomCount = b.Floors.Sum(f=>f.Rooms.Count()),
// I replaced the next line with
// CurrentWorkerCount = 10, but a solution would be nice
CurrentWorkerCount = b.Floors.Sum(f=>f.Rooms
.Sum(r=>r.RoomOccupancies
.Where(o=>!o.WorkGroup.IsFinished && o.WorkGroup.StartDate < Datetime.Now).
.Sum(w => w.NumberOfEmployees)
))),
}).ToList();
出于测试目的，我已将CurrentWorkerCount lambda替换为CurrentWorkerCount = 10，因为我可以理解是否很难转换为SQL，但是仍然无法使用RoomCount创建一个SQL语句。
使用信息级别进行记录将显示以下内容：对于至少一层以上的建筑物，“ LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估”。
然后，我有一个更大的DbCommand（太长，无法复制），然后每栋建筑物都有一个DbCommand，它计算了房间的数量。
我读到EF Core 2.1的聚合存在问题，但是我认为ORM将这个Projection转换为一个查询并不困难。
我在这里做错什么了吗，还是LINQ和EF Core的功能呢？我想我以前可以使用非核心EF轻松做到这一点。我了解了一些有关GroupBy和聚合的解决方法，但对我而言没有帮助。
更新
这是生成的日志（仅是有趣的部分）。我正在使用自定义解决方案进行过滤，排序和分页，这对于解决简单问题非常有用。在此示例中，不进行过滤，而是按建筑物名称和基本提取进行排序（跳过0，取15）。数据库中只有少量的测试数据（15座建筑物，其中一层拥有1层，另一座建筑物具有2层，其中一座具有1个房间，其中1个工作组包含100名员工）。我还使用为IsDeleted标志配置的全局过滤器进行了软删除。我不认为这些因素会影响结果，但实际上，它们确实会影响结果。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
LINQ表达式'“ Sum（）”'无法翻译，将在本地进行评估。
已执行DbCommand（“ 2” ms）[Parameters = [“ @__ p_0 ='？' （DbType = Int32），@ __ p_1 ='？' （DbType = Int32）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT CONVERT(VARCHAR(36), [x].[Id]) AS [BuildingId], [x].[Name], (
SELECT COUNT(*)
FROM [Floors] AS [x0]
WHERE ([x0].[IsDeleted] = 0) AND ([x].[Id] = [x0].[BuildingId])
) AS [FloorCount], [x].[Id]
FROM [Buildings] AS [x]
WHERE [x].[IsDeleted] = 0
ORDER BY [x].[Name]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @__ Now_2 ='？' （DbType = DateTime2），@ _ outer_Id3 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT SUM([x14].[NumberOfEmployees])
FROM [RoomOccupancys] AS [x14]
LEFT JOIN [WorkGroups] AS [k.WorkGroup2] ON [x14].[WorkGroupId] = [k.WorkGroup2].[Id]
WHERE (([x14].[IsDeleted] = 0) AND (([k.WorkGroup2].[IsFinished] = 0) AND ([k.WorkGroup2].[StartDate] < @__Now_2))) AND ([x13].[Id] = [x14].[RoomId])
)
FROM [Rooms] AS [x13]
WHERE ([x13].[IsDeleted] = 0) AND (@_outer_Id3 = [x13].[FloorId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @__ Now_2 ='？' （DbType = DateTime2），@ _ outer_Id3 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT SUM([x14].[RemainingAmount])
FROM [RoomOccupancys] AS [x14]
LEFT JOIN [WorkGroups] AS [k.WorkGroup2] ON [x14].[WorkGroupId] = [k.WorkGroup2].[Id]
WHERE (([x14].[IsDeleted] = 0) AND (([k.WorkGroup2].[IsFinished] = 0) AND ([k.WorkGroup2].[StartDate] < @__Now_2))) AND ([x13].[Id] = [x14].[RoomId])
)
FROM [Rooms] AS [x13]
WHERE ([x13].[IsDeleted] = 0) AND (@_outer_Id3 = [x13].[FloorId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
已执行DbCommand（“ 1” ms）[Parameters = [“ @_ outer_Id ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT (
SELECT COUNT(*)
FROM [Rooms] AS [x4]
WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
已执行DbCommand（“ 0” ms）[Parameters = [“ @_ outer_Id2 ='？' （DbType = Guid）“]，CommandType ='文本'，CommandTimeout = '30']”
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
最佳答案
我读到EF Core 2.1的聚合存在问题，但是我认为ORM将这个Projection转换为一个查询并不困难。
没错，EF Core在翻译GroupBy和聚合（并且不仅如此）时遇到了（并且目前仍是最新的v2.2版）问题。但不是因为“不应该是一项艰巨的任务”-尝试自己将任意表达式树转换为伪SQL，您会很快发现这是一个相当复杂的任务。
无论如何，EF Core查询翻译会随着时间的推移而不断改进，但如上所述，还远远不够完美。在这种情况下，showstopper是嵌套的聚合-总和/计数等。解决方案是将目标集展平并应用单个聚合。例如，重写您的LINQ查询，如下所示：
dbContext.Buildings.Select(b => new //BuildingDatableElementDTO()
{
BuildingId = b.Id,
Name = b.Name,
FloorCount = b.Floors.Count(),
// (1)
RoomCount = b.Floors.SelectMany(f => f.Rooms).Count(),
// (2)
CurrentWorkerCount = b.Floors
.SelectMany(f => f.Rooms)
.SelectMany(r => r.RoomOccupancies)
.Select(o => o.WorkGroup)
.Where(w => !w.IsFinished && w.StartDate < DateTime.Now)
.Sum(w => w.NumberOfEmployees),
})
.ToList();
转换为单个SQL（按预期方式）：
SELECT [e].[Id] AS [BuildingId], [e].[Name], (
SELECT COUNT(*)
FROM [Floors] AS [e0]
WHERE ([e0].[IsDeleted] = 0) AND ([e].[Id] = [e0].[BuildingId])
) AS [FloorCount], (
SELECT COUNT(*)
FROM [Floors] AS [e1]
INNER JOIN (
SELECT [e2].[Id], [e2].[FloorId], [e2].[IsDeleted], [e2].[Name]
FROM [Rooms] AS [e2]
WHERE [e2].[IsDeleted] = 0
) AS [t] ON [e1].[Id] = [t].[FloorId]
WHERE ([e1].[IsDeleted] = 0) AND ([e].[Id] = [e1].[BuildingId])
) AS [RoomCount], (
SELECT SUM([f.Rooms.RoomOccupancies.WorkGroup].[NumberOfEmployees])
FROM [Floors] AS [e3]
INNER JOIN (
SELECT [e4].*
FROM [Rooms] AS [e4]
WHERE [e4].[IsDeleted] = 0
) AS [t0] ON [e3].[Id] = [t0].[FloorId]
INNER JOIN (
SELECT [e5].*
FROM [RoomOccupancies] AS [e5]
WHERE [e5].[IsDeleted] = 0
) AS [t1] ON [t0].[Id] = [t1].[RoomId]
INNER JOIN [WorkGroups] AS [f.Rooms.RoomOccupancies.WorkGroup] ON [t1].[WorkgroupId] = [f.Rooms.RoomOccupancies.WorkGroup].[Id]
WHERE (([e3].[IsDeleted] = 0) AND (([f.Rooms.RoomOccupancies.WorkGroup].[IsFinished] = 0) AND ([f.Rooms.RoomOccupancies.WorkGroup].[StartDate] < GETDATE()))) AND ([e].[Id] = [e3].[BuildingId])
) AS [CurrentWorkerCount]
FROM [Building] AS [e]
WHERE [e].[IsDeleted] = 0