EF Core Group By

Summary: in this tutorial, you’ll learn how to use the LINQ GroupBy() method to group rows into groups and apply an aggregate function to each group in EF Core.

Introduction to the EF Core GroupBy

Sometimes, you want to group rows into groups and apply aggregation functions such as count, max, min, average, and sum to each group.

For example, you may want to group employees by departments and count the number of employees for each:

To do it, you can use the LINQ GroupBy() and Count() methods like this:

using static System.Console;
using HR;


using var context = new HRContext();

var groups = context.Employees
                .GroupBy(e => e.Department)
                .Select(group => new
                {
                    DepartmentName = group.Key.Name,
                    Headcount = group.Count()
                })
                .OrderBy(dc => dc.DepartmentName)
                .ToList();


foreach (var group in groups)
{
    WriteLine($"{group.DepartmentName,-20}{group.Headcount}");
}Code language: C# (cs)

Output:

Engineering         11
Finance             13
Marketing           13
Operations          11
Sales               12Code language: plaintext (plaintext)

How it works.

First, the GroupBy() method groups employees by departments and returns a collection of IGrouping<TKey, TElement> objects. Each grouping object has two properties: the key is the department and the element is a collection of employees who belongs to that department:

Sales
- John Doe
- Emily Brown
...
Marketing
- Jane Smith
- William Taylor
- Daniel Clark
...Code language: plaintext (plaintext)

Second, the Select() method creates an anonymous object with two properties DepartmentName and Headcount. It assigns the Name property of the Department object to the DepartmentName and the number of employees per group to the Headcount property:

Sales               12
Marketing           13
...Code language: plaintext (plaintext)

Third, the OrderBy() method sorts the groups by DepartmentName.

Finally, the ToList() method executes the query and returns the result.

EF Core generates the following SQL statement that uses the GROUP BY clause to group employees by departments and applies a COUNT aggregate function to each group:

SELECT 
  [d].[Name] AS [DepartmentName], 
  COUNT(*) AS [Headcount] 
FROM 
  [Employees] AS [e] 
  INNER JOIN [Departments] AS [d] ON [e].[DepartmentId] = [d].[Id] 
GROUP BY 
  [d].[Id], 
  [d].[Name] 
ORDER BY 
  [d].[Name]Code language: SQL (Structured Query Language) (sql)

To apply a condition to every group, you use the LINQ Where() method. For example, the following selects departments that have more than 11 employees:

using static System.Console;
using HR;


using var context = new HRContext();

var groups = context.Employees
                .GroupBy(e => e.Department)
                .Select(group => new
                {
                    DepartmentName = group.Key.Name,
                    Headcount = group.Count()
                })
                .Where(group => group.Headcount > 11)
                .OrderBy(dc => dc.DepartmentName)
                .ToList();


foreach (var group in groups)
{
    WriteLine($"{group.DepartmentName,-20}{group.Headcount}");
}Code language: C# (cs)

Output:

Finance             13
Marketing           13
Sales               12Code language: plaintext (plaintext)

In this example, EF Core generates a SQL statement that uses a GROUP BY clause with a HAVING clause:

SELECT 
  [d].[Name] AS [DepartmentName], 
  COUNT(*) AS [Headcount] 
FROM 
  [Employees] AS [e] 
  INNER JOIN [Departments] AS [d] ON [e].[DepartmentId] = [d].[Id] 
GROUP BY 
  [d].[Id], 
  [d].[Name] 
HAVING 
  COUNT(*) > 11 
ORDER BY 
  [d].[Name]Code language: SQL (Structured Query Language) (sql)

Summary

• Use the LINQ GroupBy() method to form a query that uses the GROUP BY clause to group rows into groups.