Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Database Level Grouping in Entity Framework Core 2.1

DZone's Guide to

Database Level Grouping in Entity Framework Core 2.1

When using GroupBy with Entity Framework Core 2.1, make sure you test your queries to make sure they behave like you expected.

· Database Zone ·
Free Resource

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:
database ,entity framework ,core 2.1 ,asp.net ,grouping

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}