.NET Core EF Core(Entity Framework) 实现分组查询(group by)
时间:2022-08-23
1、EF Core中实现分组代码
var headerTask = cases .Select(c => new { c.Flag, c.YourKey //其它属性 }) .GroupBy(c => c.YourKey, (k, g) => new CaseHeader { TotalCases = g.Count(), //... 其他一些聚合函数 TotalFlagCases = g.Sum(b => a.Flag ? 1 : 0) });
上面代码EF Core代码生成的SQL语名:
SELECT COUNT(*) AS [TotalCases], SUM(CASE WHEN [c].[Flag] = 1 THEN 1 ELSE 0 END) AS [TotalFlagCases] FROM [Cases] AS [c] GROUP BY [c].[YourKey]
2、Entity Framework中实现分组的代码
Consumers.GroupBy(c => c.Gender) .Select(g => new { Key = g.Key, Count = g.Count() }) .ToDictionary(g => g.Key, g => g.Count)
上面代码对应生成的SQL语句:
SELECT COUNT(*) AS [Count], [t0].[Gender] AS [Key]
FROM [Consumers] AS [t0]
GROUP BY [t0].[Gender]
多列group by汇总求和:
var result = DataSummaryRepository.FindBy(x => x.UserID == argMemberNo && x.SummaryDate <= argEndDate && x.SummaryDate >= argStarDate).OrderByDescending(x => x.SummaryDate).GroupBy(x => new { x.SummaryDate, x.UserID }).Select(g => new { PV = g.Sum(x => x.PV), UV = g.Sum(x => x.UV), OrderQunantity = g.Sum(x => x.OrderQunantity), OrderAmount = g.Sum(x => x.OrderAmount), CommissionEstimate = g.Sum(x => x.CommissionEstimate), AvaliableCommission = g.Sum(x => x.AvaliableCommission), HistoryCommission = g.Sum(x => x.HistoryCommission), SummaryDate = g.Key.SummaryDate, UserID = g.Key.UserID });
特别声明:本站部分内容收集于互联网是出于更直观传递信息的目的。该内容版权归原作者所有,并不代表本站赞同其观点和对其真实性负责。如该内容涉及任何第三方合法权利,请及时与824310991@qq.com联系,我们会及时反馈并处理完毕。