我有一个带有 EF Core 2.2 Code-First DB 的 ASP.NET Core 2.2 项目。我有以下实体:
- 建筑物,基本上是一个带有一些其他重要数据的地址。
- Floor,包含楼层号。一栋建筑物可以有多层。一个楼层必须恰好有一个建筑物所在的位置。
- 房间,有号码。一个楼层可以有多个房间。一个房间必须只有一层。
- WorkGroup,包含该组有多少员工、该组是否仍然活跃、该组什么时候开始运营(可以是将来)。
- 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 将此投影转换为一个查询应该不是一项艰巨的任务。
我是在那里做错了什么还是这些是 LINQ 和 EF Core 的功能?我认为以前使用非 Core EF 可以轻松做到这一点。我读到了有关 GroupBy 和聚合的一些解决方法,但它对我的情况没有帮助。
UPDATE
这是生成的日志(仅有趣的部分)。我正在使用自定义解决方案进行过滤、排序和分页,该解决方案非常适合解决简单的问题。本例中没有过滤,按建筑物名称和基本获取排序(跳过 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) [参数=["@__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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@__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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@__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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_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) [参数=["@_outer_Id2='?' (DbType = Guid)"], CommandType='文本', CommandTimeout='30']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])