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
    public string Name { get; set; }

public class Product : BaseEntity
    public string Name { get; set; }

    public string Description { get; set; }

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

Liked this post? Empower your friends by sharing it!
Categories: ASP.NET

View Comments (0)

Related Post