在ASP.NET Entity Framework Core中使用GroupBy()和Min()的方法

问题1：实现等价于指定SQL的EF查询 #

原SQL实现的是按WorkingArea分组，获取每个区域的最小佣金。你之前的代码仅赋值了Commission，未包含分组键WorkingArea，且直接实例化Agent实体存在隐患（实体其他属性未赋值，易引发异常）。推荐使用匿名类型或自定义DTO承载结果：

方法1：使用匿名类型 #

var result = _db.Agents
    .GroupBy(a => a.WorkingArea)
    .Select(g => new 
    {
        WorkingArea = g.Key,
        MinCommission = g.Min(a => a.Commission)
    })
    .ToList();

方法2：使用自定义DTO（便于复用） #

先定义DTO类：

public class AgentAreaMinCommissionDto
{
    public string WorkingArea { get; set; }
    public decimal MinCommission { get; set; }
}

再执行查询：

List<AgentAreaMinCommissionDto> result = _db.Agents
    .GroupBy(a => a.WorkingArea)
    .Select(g => new AgentAreaMinCommissionDto
    {
        WorkingArea = g.Key,
        MinCommission = g.Min(a => a.Commission)
    })
    .ToList();

以上代码可生成与原SQL完全一致的结果，包含每个区域对应的最小佣金。

问题2：在结果中添加PhoneNo列（注意原SQL的合法性问题） #

你给出的SQL SELECT WorkingArea, MIN(Commission), PhoneNo FROM agents GROUP BY WorkingArea 不符合标准SQL规范：PhoneNo既未出现在GROUP BY子句中，也未被聚合函数包裹，数据库无法确定分组后返回哪一条记录的PhoneNo。

以下针对两种合理需求给出实现方案：

需求A：获取每个区域中佣金最低的代理的PhoneNo #

方式1：分组找最小佣金后关联原表

var result = _db.Agents
    .GroupBy(a => a.WorkingArea)
    .Select(g => new 
    {
        WorkingArea = g.Key,
        MinCommission = g.Min(a => a.Commission)
    })
    .Join(_db.Agents,
          g => new { g.WorkingArea, g.MinCommission },
          a => new { a.WorkingArea, a.Commission },
          (g, a) => new 
          {
              g.WorkingArea,
              g.MinCommission,
              a.PhoneNo
          })
    .ToList();

方式2：使用窗口函数（EF Core 3.0+支持）

var result = _db.Agents
    .Select(a => new 
    {
        a.WorkingArea,
        a.Commission,
        a.PhoneNo,
        RowNum = EF.Functions.RowNumber()
            .Over(PartitionBy(a.WorkingArea)
                  .OrderBy(a.Commission))
    })
    .Where(x => x.RowNum == 1)
    .Select(x => new 
    {
        x.WorkingArea,
        MinCommission = x.Commission,
        x.PhoneNo
    })
    .ToList();

需求B：返回分组内任意一条记录的PhoneNo（结果不可控，不推荐） #

var result = _db.Agents
    .GroupBy(a => a.WorkingArea)
    .Select(g => new 
    {
        WorkingArea = g.Key,
        MinCommission = g.Min(a => a.Commission),
        PhoneNo = g.FirstOrDefault()?.PhoneNo
    })
    .ToList();

内容的提问来源于stack exchange，提问作者Lakshitha Kanchana