带有EF Core Linq2Sql的聚合的聚合

本文介绍了带有EF Core Linq2Sql的聚合的聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有EF Core 2.2 Code-First DB的ASP.NET Core 2.2项目.我有以下实体:

I have a ASP.NET Core 2.2 project with EF Core 2.2 Code-First DB. I have the following entities:

• 建筑物，基本上是其他一些重要数据的地址.
• 楼层，其中包含楼层编号.一栋建筑物可以有多个楼层.地板必须精确地位于其所在的建筑物中.
• 房间，有一个数字.一个楼层可以有多个房间.一间房间必须恰好有一层.
• WorkGroup，其中包含该组中有多少名员工，该组是否仍处于活动状态以及该组何时开始运营(可能在将来).
• RoomOccupancy，它是一个工作组和一个房间之间的联接表，并显示该工作组在/曾经/将在哪个房间中.

我需要列出建筑物的列表，其中包括建筑物名称，建筑物的楼层数，建筑物的房间数(不是楼层)以及当前在建筑物中工作的人数.

I need a list of the buildings with the buildings name, how many floors it has, how many rooms the building has (not a floor), and how many people currently works int the building.

当前，我能够获取所有数据，但是转换后的SQL并不是最佳选择，并且需要多次访问数据库.对于该问题，我能够手动编写一个SQL select语句(带有内部select)，所以我知道这可以通过一个查询来实现.

Currently I'm able to get all the data, but the translated SQL is not optimal and takes many trips to the DB. I was able to write one SQL select statement (with inner select) by hand for the problem, so I know this should be possible with one query.

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语句.

For testing purposes I have replaced the CurrentWorkerCount lambda with CurrentWorkerCount = 10, because I can understand if it's hard to translate to SQL, but it still fails to create one SQL statement with the RoomCount.

使用信息级别进行登录将显示此信息:对于每层至少有一层的建筑物，"LINQ表达式'"Sum()"'无法翻译，将在本地进行评估". 然后，我有一个更大的DbCommand(太长，无法复制)，然后每栋建筑物都有一个DbCommand，它计算了房间的数量.

Logging with info level shows this: "The LINQ expression '"Sum()"' could not be translated and will be evaluated locally" for every building that has at least one floor. Then I have one bigger DbCommand (too long to copy), then one DbCommand for every building, which counts the number of rooms.

我了解到EF Core 2.1的聚合存在问题，但是我认为ORM将Projection转换为一个查询并不困难.

I read that there are problems with the aggregates with EF Core 2.1, but I think it shouldn't be a hard task for the ORM to translate this Projection into one query.

我在这里做错什么了吗，或者这是LINQ和EF Core的功能吗?我想我以前可以使用非核心EF轻松做到这一点.我了解了一些有关GroupBy和聚合的解决方法，但对我而言没有帮助.

Am I doing something wrong there or these are the capabilities of the LINQ and the EF Core? I think I could easily do that with the non-Core EF previously. I read about some workarounds for GroupBy and aggregates, but it didn't help in my case.

更新

这是生成的日志(仅是有趣的部分).我正在使用自定义解决方案进行筛选，排序和分页，这对于解决简单问题非常有用.在此示例中，不进行过滤，而是按建筑物名称和基本提取进行排序(跳过0，取15).数据库中只有少量的测试数据(15栋建筑物，其中一栋为1层，另一栋为2层，其中一栋为1室，其中一个工作组为100名员工).我还使用为IsDeleted标志配置的全局过滤器进行了软删除.我不认为这些因素会影响结果，但是在这里，也许确实会影响结果.

Here is the generated log (only the interesting parts). I'm using a custom solution for filtering, sorting and paging which works great with simple problems. No filtering in this example, sorting by the buildings name and basic fetch (skip 0 take 15). There is only a minimal amount of test data in the database (15 buildings one has 1 floor, another one has 2, of which one has 1 room, which has 1 workgroup with 100 employees). I also have soft delete with a global filter configured for the IsDeleted flag. I don't think these things affect the results, but here they are, maybe they do.

• 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转换为一个查询并不困难.

I read that there are problems with the aggregates with EF Core 2.1, but I think it shouldn't be a hard task for the ORM to translate this Projection into one query.

您是正确的，EF核心在翻译GroupBy和聚合(并且不仅如此)时遇到了(并且仍然有-当前的最新v2.2)问题.但是不是不应该是一项艰巨的任务" -尝试自己将任意表达式树转换为伪SQL，您会很快发现这是一个非常复杂的任务.

You are right that EF Core had (and still have - the latest at this time v2.2) problems translating GroupBy and aggregates (and not only). But not for "shouldn't be a hard task" - try converting arbitrary expression tree to pseudo SQL yourself and you'll quickly find that it is quite complicated task.

无论如何，EF Core查询翻译会随着时间的推移而不断改进，但如上所述，还远远不够完美.在这种情况下，showstopper是嵌套的聚合-总和/计数等.解决方案是展平目标集并应用单个聚合.例如，重写您的LINQ查询，如下所示:

Anyway, EF Core query translation improves over the time, but as mentioned, is far from perfect. The showstopper in this case are nested aggregates - sum of sum/count etc. The solution is to flatten the target set and apply single aggregate. For instance, rewriting your LINQ query as follows:

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(按预期方式):

is translated to a single SQL (as expected):

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