Grouping : Entity Framework Core

Overview

Here's how you can group data and project the results in Entity Framework Core, using an example scenario.

Scenario

Suppose you have the following models:

public class Order
{
    public int Id { get; set; }
    public string Customer { get; set; }
    public decimal Total { get; set; }
    public DateTime Date { get; set; }
}

You want to group orders by customer, and get:

• Customer name
• Total number of orders
• Sum of order totals
• Latest order date

Example: Group + Project into Anonymous Type

var customerSummaries = await _context.Orders
    .GroupBy(o => o.Customer)
    .Select(g => new 
    {
        Customer = g.Key,
        OrderCount = g.Count(),
        TotalSpent = g.Sum(o => o.Total),
        LastOrderDate = g.Max(o => o.Date)
    })
    .ToListAsync();

Sample Output

[
    { Customer = "Alice", OrderCount = 5, TotalSpent = 200.00m, LastOrderDate = 2025-07-30 },
    { Customer = "Bob", OrderCount = 2, TotalSpent = 75.00m, LastOrderDate = 2025-07-29 }
]

Explanation

• .GroupBy(o => o.Customer) groups the orders by customer name.
• Each group g contains:
  • g.Key → the grouping key (customer name)
  • g.Count() → number of items in group
  • g.Sum() / g.Max() → aggregates over the group
• .Select(...) projects the group into a new anonymous type.

Optional: Project into DTO instead

public class CustomerOrderSummary
{
    public string Customer { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalSpent { get; set; }
    public DateTime LastOrderDate { get; set; }
}

var summaries = await _context.Orders
    .GroupBy(o => o.Customer)
    .Select(g => new CustomerOrderSummary
    {
        Customer = g.Key,
        OrderCount = g.Count(),
        TotalSpent = g.Sum(o => o.Total),
        LastOrderDate = g.Max(o => o.Date)
    })
    .ToListAsync();

Gotchas to Avoid

• All grouped projections must be translatable to SQL — avoid calling custom C# methods in the projection.
• Watch out for nullable aggregates like Sum() or Max() on empty groups (they can return null if not handled).

Summary

Task	Approach
Group by field	.GroupBy(x => x.Field)
Aggregate within group	.Sum(), .Count(), .Max(), .Min()
Project into new shape	.Select(g => new { ... })
Use in EF Core	Must be translatable to SQL (no complex C# logic)

Multi-level group

Multi-level grouping (also called composite grouping) is where you group by more than one field — for example, by month and customer.

Multi Level Grouping Scenario

You want to analyze monthly order summaries per customer:

• Group by year and month
• Then by customer
• Show customer, month, total spent, and number of orders

Assume you have this model:

public class Order
{
    public int Id { get; set; }
    public string Customer { get; set; }
    public decimal Total { get; set; }
    public DateTime Date { get; set; }
}

Example: Multi-level Grouping and Projection

var summaries = await _context.Orders
    .GroupBy(o => new 
    {
        o.Date.Year,
        o.Date.Month,
        o.Customer
    })
    .Select(g => new 
    {
        Customer = g.Key.Customer,
        Year = g.Key.Year,
        Month = g.Key.Month,
        TotalSpent = g.Sum(x => x.Total),
        OrderCount = g.Count()
    })
    .OrderBy(r => r.Year).ThenBy(r => r.Month).ThenBy(r => r.Customer)
    .ToListAsync();

Example Output

[
    { Customer = "Alice", Year = 2025, Month = 6, TotalSpent = 120.00, OrderCount = 3 },
    { Customer = "Bob",   Year = 2025, Month = 6, TotalSpent = 75.00,  OrderCount = 2 },
    { Customer = "Alice", Year = 2025, Month = 7, TotalSpent = 200.00, OrderCount = 4 }
]

Optional DTO Version

public class MonthlyCustomerSummary
{
    public string Customer { get; set; }
    public int Year { get; set; }
    public int Month { get; set; }
    public decimal TotalSpent { get; set; }
    public int OrderCount { get; set; }
}

var summaries = await _context.Orders
    .GroupBy(o => new { o.Date.Year, o.Date.Month, o.Customer })
    .Select(g => new MonthlyCustomerSummary
    {
        Customer = g.Key.Customer,
        Year = g.Key.Year,
        Month = g.Key.Month,
        TotalSpent = g.Sum(o => o.Total),
        OrderCount = g.Count()
    })
    .ToListAsync();

Notes

• Grouping by new { ... } lets you combine fields — you can group by any combination of columns.
• You can’t nest .GroupBy() calls effectively in EF Core (like LINQ-to-Objects), so composite keys like this are the best approach.
• Keep .GroupBy(...) + .Select(...) together to ensure it's translated to SQL.

Multi Level Grouping Summary

Task	Approach
Multi-level group	.GroupBy(new { Year, Month, ... })
Use aliases	g.Key.Month, g.Key.Customer, etc.
Aggregate per group	.Sum(), .Count(), etc.
Output shape	Anonymous type or DTO