.NET Core EF Core(Entity Framework) 实现分组查询(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
            });