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

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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.

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

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