A new improvement for the new Entity Framework Core 2.1 release is that now the Group By statements will be executed at the database level. This means that when we make a Group By in LINQ to a DbSet, the resulting query will contain the appropriate Group By. Let’s see a simple first example.

using (var context = new ApplicationDbContext())
{

var report = context.Students
.GroupBy(x => new { x.IsDeleted})
.Select(x => new { x.Key, Count = x.Count() }).ToList();

}

The idea of this sentence is to ask for a list of the students, grouped according to the value of the IsDeleted field, and then we use Select to project this result into an object which will have the key of the grouping (if the student has been deleted or not, that is, true or false), and also the number of students in this group. Finally we make this statement run using the ToList function. The resulting query is the following:

SELECT [s].[IsDeleted], COUNT(*) AS [Count]
FROM [Students] AS [s]
GROUP BY [s].[IsDeleted]

We are not limited only to Group By, we can also use a Having. The Having serves to filter according to the result of aggregate functions. The aggregate functions are those that work on a collection of records. Example: Count is an aggregate function because its function is to count several records. Sum is another aggregate function because its function is to add values from different registers.

For example, suppose we want to group students according to their year of birth, however, we only want to take into account those groups that contain two or more people.

using (var context = new ApplicationDbContext())
{

var report = context.Students
.GroupBy(x => new { x.Birthdate.Year })
.Where(x => x.Count() >= 2)
.Select(x => new { x.Key, Count = x.Count() })
.ToList();

}

Notice that we use a Where which is working with the count function, which is an aggregate function. This causes a Having to be placed in the resulting query:

SELECT DATEPART(year, [s].[Birthdate]) AS [Year], COUNT(*) AS [Count]
FROM [Students] AS [s]
GROUP BY DATEPART(year, [s].[Birthdate])
HAVING COUNT(*) >= 2