.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 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
            });

了解更多分析及数据抓取可查看：
http://data.yisurvey.com:8989/
特别说明：本文旨在技术交流，请勿将涉及的技术用于非法用途，否则一切后果自负。如果您觉得我们侵犯了您的合法权益，请联系我们予以处理。