如何在实体框架核心ASP.NET中使用GroupBy()和Min()

Question

假设我的数据库表Agents如下，

+------------+----------------------+--------------------+------------+-----------------+---------+
| AgentCode  | AgentName            | WorkingArea        | Commission | PhoneNo         | Country |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

我需要查询的内容如下(为了更好地理解，请使用SQL(。

SELECT WorkingArea, MIN(Commission) 
FROM agents 
GROUP BY WorkingArea;

其结果是：-

WorkingArea                         MIN(Commission)
----------------------------------- ---------------
San Jose                                        .12
Torento                                         .15
London                                          .13
Hampshair                                       .11
New York                                        .12
Brisban                                         .13
Bangalore                                       .14
Chennai                                         .14
Mumbai                                          .11

如何对.NET实体框架执行同样的操作

我尝试了以下方法。但它只给了我MIN(委员会(的争吵。

IEnumerable<Agent> AgentList = _db.Agents
.GroupBy(fields => fields.WorkingArea)
.Select(fields => new Agent
{
Commission = fields.Min(fields => fields.Commission)
});

// If my Model name is Agent its corresponding database table name will be Agent**s**.

更新1---------------------------------------------------------------------------------------------------

1.如何使用.NET实体框架执行与上述sql查询等效的操作

2.如果我还想在结果中添加PhoneNo列，该怎么办？类似

SELECT WorkingArea, MIN(Commission), PhoneNo 
FROM agents 
GROUP BY WorkingArea;

Answer 1

结果输出需要另一种类型(而不是Agent(。定义两字段类

class WorkingAreaCommission
{
public string WorkingArea { get; set; }
public double Commission { get; set; }
}

或使用匿名类型：

var AgentList = _db.Agents
.GroupBy(fields => fields.WorkingArea)
.Select(fields => new
{
WorkingArea = fields.Key,
Commission = fields.Min(fields => fields.Commission)
});

更新(回答评论中的问题(

如果您需要的结果不仅仅包含关键字和最小值，请按需要最小值的字段排序，并从第一条记录中提取所有需要的字段：

var AgentList = _db.Agents
.GroupBy(fields => fields.WorkingArea)
.Select(fields => fields
.OrderBy(x => x.Commission)
.Select(x => new {
WorkingArea = x.WorkingArea,
PhoneNo = x.PhoneNo,
Commission = x.Commission
})
.First()
);