EFCore2.2中使用Group By的那些坑及解决方法
背景
在后端使用EFCore进行数据库操作的时候，不可避免的要进行Group By操作，在进行Group By后有时候进行Sum的操作的时候EFCore是不能按照我们的预期生成正确的SQL的，而且这个问题EFCore的官方也没有给出一个好的解决方式，那么在使用EFCore2.2进行开发的时候这些问题又不可避免，那么我们该如何规避这个问题，并且使之生成我们期望的SQL语句呢，本篇我们我们就这个出现这个问题的现象以及解决方案来进行分析，来看看在现阶段到底该如何解决这个问题。
正常情况
我们先来看一下能够正常生成SQL的EFCore的写法，这种情况下会生成符合预期的SQL语句并且没有什么问题，我们先来看看正常情况的EFCore的代码。
/// <summary>
/// 查询月度辅导任务清单列表
/// </summary>
/// <param name="input">查询输入</param>
/// <param name="pageRequest">分页请求</param>
/// <returns>带分页的月度任务清单</returns>
public async Task<Page<GetCoachTaskDetailOutput>> GetCoachTaskDetailExAsync(GetCoachTaskDetailInput input, PageRequest pageRequest) {
var queryResults = from v in from coachTask in _coachTaskRepository.GetAll()
.WhereIf(!string.IsNullOrWhiteSpace(input.Code), c => c.Code.Contains(input.Code))
.WhereIf(input.Year.HasValue, c => c.Year == input.Year)
.WhereIf(input.Quarter.HasValue, c => c.Quarter == input.Quarter)
.WhereIf(input.Month.HasValue, c => c.Month == input.Month)
.WhereIf(!string.IsNullOrWhiteSpace(input.MarketingResponsibleName),
c => c.MarketingResponsibleName.Contains(input.MarketingResponsibleName))
.WhereIf(input.MarketingDepartmentIds.Any(), c => input.MarketingDepartmentIds.Contains(c.MarketingDepartmentId))
join coachTaskDetail in _coachTaskDetailRepository.GetAll()
.WhereIf(!string.IsNullOrWhiteSpace(input.DealerCode), d => d.DealerCode.Contains(input.DealerCode))
.WhereIf(input.Status.Any(), d => input.Status.Contains(d.Status))
.WhereIf(input.ReviewStatus.HasValue, d => d.ReviewStatus == input.ReviewStatus)
.WhereIf(input.BeginActualCoachTime.HasValue, d => input.BeginActualCoachTime <= d.ActualCoachTime)
.WhereIf(input.EndActualCoachTime.HasValue, d => d.ActualCoachTime <= input.EndActualCoachTime)
on coachTask.Id equals coachTaskDetail.CoachTaskId
join coachTaskItemDetail in _coachTaskItemDetailRepository.GetAll()
.WhereIf(input.Type.HasValue, i => i.Type == input.Type)
on coachTaskDetail.Id equals coachTaskItemDetail.CoachTaskDetailId
select new {
coachTaskDetail.Id,
coachTask.Code,
coachTask.Year,
coachTask.Quarter,
coachTask.Month,
coachTaskDetail.DealerCode,
coachTaskDetail.DealerName,
coachTaskDetail.Status,
coachTaskDetail.ReviewStatus,
coachTask.MarketingDepartmentName,
coachTask.MarketingResponsibleName,
coachTaskDetail.ActualerCoachName,
coachTaskDetail.ActualCoachTime,
coachTaskItemDetail.StandardScore,
coachTaskItemDetail.IsQualified
}
group v by new {
v.Id,
v.Code,
v.Year,
v.Quarter,
v.Month,
v.DealerCode,
v.DealerName,
v.Status,
v.ReviewStatus,
v.MarketingDepartmentName,
v.MarketingResponsibleName,
v.ActualerCoachName,
v.ActualCoachTime,
} into tempGroups
select new GetCoachTaskDetailGroupByResultModel {
Id = tempGroups.Key.Id,
Code = tempGroups.Key.Code,
Year = tempGroups.Key.Year,
Quarter = tempGroups.Key.Quarter,
Month = tempGroups.Key.Month,
DealerCode = tempGroups.Key.DealerCode,
DealerName = tempGroups.Key.DealerName,
Status = tempGroups.Key.Status,
ReviewStatus = tempGroups.Key.ReviewStatus,
MarketingDepartmentName = tempGroups.Key.MarketingDepartmentName,
MarketingResponsibleName = tempGroups.Key.MarketingResponsibleName,
ActualerCoachName = tempGroups.Key.ActualerCoachName,
ActualCoachTime = tempGroups.Key.ActualCoachTime,
//对应的月度辅导任务项目清单中标准分值
ScoreTotal = tempGroups.Sum(r => r.StandardScore ?? 0),
NoQualifiedQty = tempGroups.Sum(r => r.IsQualified == false ? 1 : 0)
};
//获取分组后的结果
var totalCount = await queryResults.CountAsync();
var pagedResults = await queryResults.ProjectTo<GetCoachTaskDetailOutput>(_autoMapper.ConfigurationProvider).PageAndOrderBy(pageRequest).ToListAsync();
pagedResults.ForEach(item => {
if (item.ScoreTotal < 0) {
item.ScoreTotal = 0;
}
});
return new Page<GetCoachTaskDetailOutput>(pageRequest, totalCount, pagedResults);
}
上面的写法非常简单就是三张表进行join然后进行分组，只不过是最终的结果中ScoreTotal和NoQualifiedQty需要根据分组的结果进行求和，对于这种比较简单的分组结果EFCore是完全能够生成正确的SQL,我们来看看此时EFCore正确生成的SQL。
SELECT TOP (20)
[coachTaskDetail].[ActualCoachTime],
[coachTaskDetail].[ActualerCoachName],
[coachTask].[Code],
[coachTaskDetail].[DealerCode],
COALESCE([coachTaskDetail].[Id], '00000000-0000-0000-0000-000000000000') AS [Id],
[coachTask].[MarketingDepartmentName],
[coachTask].[MarketingResponsibleName],
[coachTask].[Month],
COALESCE(SUM(CASE
WHEN [coachTaskItemDetail].[IsQualified] = 0
THEN 1
ELSE 0
END), 0) AS [NoQualifiedQty],
[coachTask].[Quarter],
COALESCE([coachTaskDetail].[ReviewStatus], 0) AS [ReviewStatus],
COALESCE(SUM(COALESCE([coachTaskItemDetail].[StandardScore], 0.0)), 0.0) AS [ScoreTotal],
[coachTaskDetail].[DealerName] AS [ShortName],
COALESCE([coachTaskDetail].[Status], 0) AS [Status],
COALESCE([coachTask].[Year], 0) AS [Year]
FROM [CoachTask] AS [coachTask]
INNER JOIN [CoachTaskDetail] AS [coachTaskDetail] ON [coachTask].[Id] = [coachTaskDetail].[CoachTaskId]
INNER JOIN [CoachTaskItemDetail] AS [coachTaskItemDetail]
ON [coachTaskDetail].[Id] = [coachTaskItemDetail].[CoachTaskDetailId]
GROUP BY [coachTaskDetail].[Id], [coachTask].[Code], [coachTask].[Year], [coachTask].[Quarter], [coachTask].[Month],
[coachTaskDetail].[DealerCode], [coachTaskDetail].[DealerName], [coachTaskDetail].[Status],
[coachTaskDetail].[ReviewStatus], [coachTask].[MarketingDepartmentName], [coachTask].[MarketingResponsibleName],
[coachTaskDetail].[ActualerCoachName], [coachTaskDetail].[ActualCoachTime]
ORDER BY [coachTask].[Code] DESC
未完，待续......
