c# - 带有EF Core Linq2Sql的聚合的聚合

Question

我有一个带有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])

Answer 1

我读到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