How to use GroupBy() and Min() with Entity Framework Core ASP .NET

Question

Let's say my database table Agents is the following,

+------------+----------------------+--------------------+------------+-----------------+---------+
| 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    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

What I exactly need to query is as the following(in SQL for better understanding).

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

And its result is:-

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

How can I do the same with .NET Entity Framework?

I tried the following. But it gave me only the MIN(COMMISSION) row.

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**.

Update 1--------------------------------------------------------------------------------------------------

1. How can I do the equivalent to the sql query above, with .NET Entity Framework?

2. What if I want add also the PhoneNo column to the result? like

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

Answer 1

You need another type (not Agent) for the result output. Either define a two-field class

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

or use anonymous type:

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

Update (to answer a question from comments)

If you need result containing more than just key and minimal values, sort by the field you need minimal value from and extract all required fields from the first record:

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()
    );