Database level grouping in Entity Framework Core 2.1

Previous versions of Entity Framework Core doesn’t support database level grouping and LINQ GroupBy is translated locally. Entity Framework Core 2.1 translates LINQ GroupBy to database query in most of cases. Here is the example.

To illustrate GroupBy translation I’m using database and some code from my multitenant ASP.NET Core sample solution. There is simple database with two entities not related to ASP.NET Identity.

Here are classes for these tables.

public class ProductCategory : BaseEntity
{
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
}
 
public class Product : BaseEntity
{
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
 
    [Required]
    [StringLength(512)]
    public string Description { get; set; }
 
    [Required]
    public ProductCategory Category { get; set; }
}

Let’s try to query these tables and find out how many products each category has.

var grouped = from b in _context.Products
              group b.Id by b.Category.Name into g
              select new
              {
                  Key = g.Key,
                  Products = g.Count()
              };
var result = grouped.ToList();

When running the query this is what is built by Entity Framework Core 2.1.

Here is SQL query generated by Entity Framework. Categories are joined as a query because my sample uses global query filters to make sure all tenants load only their own data.

SELECT [t].[Name] AS [Key], COUNT(*) AS [Products]
FROM [Products] AS [e]
INNER JOIN (
    SELECT [e0].*
    FROM [Categories] AS [e0]
    WHERE [e0].[TenantId] = @__ef_filter__Id_1
) AS [t] ON [e].[CategoryId] = [t].[Id]
WHERE [e].[TenantId] = @__ef_filter__Id_0
GROUP BY [t].[Name]

There are some situations when LINQ OrderBy is not translated to database level GROUP BY but grouping is done locally. If you are interested in these edge cases then take a look at Entity Framework Core filtered issue tracker here.

Wrapping up

Entity Framework Core 2.1 translates LINQ GroupBy to database level grouping in most of cases but there are some exceptions. But be aware – there are cases when grouping is done locally. When using GroupBy with Entity Framework Core 2.1 make sure you test your queries to make sure they behave like you expected.