开发手册 欢迎您!
软件开发者资料库

.NET Core EF Core(Entity Framework) 实现分组查询(group by)

本文主要介绍在.NET Core中使用EF Core实现分组查询(group by)的方法。

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 0END) 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            });