Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 41 ยท Querying with LINQ

Joins, Grouping and Aggregates in EF Core

Write inner and left joins, use navigation properties instead of joins, group with GroupBy and compute Count, Sum and Average in SQL, with the translation rules and pitfalls explained.

Reports, dashboards and summaries need data from several tables combined and totalled: sales per customer, orders per month, the top five products. In SQL that means JOIN and GROUP BY. In EF Core you can often avoid writing joins at all, and when you do group, the aggregate must be translated to SQL so the database does the heavy lifting.

The real EF Core cannot run on this site, so queries are read-only. The runnable stand-ins execute the same LINQ shapes over in-memory lists, which behave the same way for these operators.

Prefer navigation properties

If you configured a relationship (see One-to-Many Relationships), you rarely write a join:

C#
var rows = await db.Orders
    .Where(o => o.Customer.City == "Berlin")        // implicit inner join
    .Select(o => new { o.Id, Customer = o.Customer.Name, o.Total })
    .ToListAsync();

EF Core produces FROM Orders JOIN Customers ON ... for you. This is shorter and keeps the relationship rules in one place, the model.

Explicit joins

For tables without a configured relationship, use Join or query syntax:

C#
var q = from o in db.Orders
        join c in db.Customers on o.CustomerId equals c.Id
        select new { o.Id, c.Name, o.Total };

The method form is db.Orders.Join(db.Customers, o => o.CustomerId, c => c.Id, (o, c) => new { ... }). Both give an inner join: orders without a customer do not appear.

Left join

LINQ has no LeftJoin keyword in older code; the classic pattern uses GroupJoin and DefaultIfEmpty. EF Core 10 added LeftJoin and RightJoin operators, which are much clearer:

C#
// EF Core 10
var rows = db.Customers.LeftJoin(
    db.Orders,
    c => c.Id, o => o.CustomerId,
    (c, o) => new { c.Name, OrderId = (int?)o.Id });

// Classic pattern (all versions)
var rows2 = from c in db.Customers
            join o in db.Orders on c.Id equals o.CustomerId into g
            from o in g.DefaultIfEmpty()
            select new { c.Name, OrderId = (int?)o.Id };

Customers without orders appear with null for the order columns. Remember to make the right-hand values nullable.

Grouping

C#
var perCustomer = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        Orders = g.Count(),
        Revenue = g.Sum(o => o.Total),
        Average = g.Average(o => o.Total),
        Biggest = g.Max(o => o.Total)
    })
    .ToListAsync();

This becomes SELECT CustomerId, COUNT(*), SUM(Total), AVG(Total), MAX(Total) FROM Orders GROUP BY CustomerId. The key rule: the Select after GroupBy must use only the key and aggregate functions (Count, Sum, Average, Min, Max) for it to translate to a single SQL statement.

HAVING

A Where placed after GroupBy becomes HAVING:

C#
var bigSpenders = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Where(g => g.Sum(o => o.Total) > 1000m)
    .Select(g => new { g.Key, Total = g.Sum(o => o.Total) })
    .ToListAsync();

Group by several columns and by dates

C#
var monthly = await db.Orders
    .GroupBy(o => new { o.PlacedAt.Year, o.PlacedAt.Month })
    .Select(g => new { g.Key.Year, g.Key.Month, Orders = g.Count(), Revenue = g.Sum(o => o.Total) })
    .OrderBy(x => x.Year).ThenBy(x => x.Month)
    .ToListAsync();

The pitfall: grouping that returns the group's rows

C#
var groups = await db.Orders.GroupBy(o => o.CustomerId).ToListAsync();   // not translatable as one query

Asking for the elements of each group (not just aggregates) cannot be expressed in one SQL statement. Older EF Core versions silently loaded the whole table and grouped in memory; current versions throw or issue a poor plan. To get "group plus its rows", query from the one side with a navigation (Customers.Select(c => new { c.Name, c.Orders })) or aggregate first and then fetch details.

Aggregates without grouping

C#
decimal? maxTotal = await db.Orders.MaxAsync(o => (decimal?)o.Total);   // null when no rows
decimal sum = await db.Orders.SumAsync(o => o.Total);                    // 0 when no rows
double avg = await db.Orders.AverageAsync(o => (double)o.Total);         // throws when no rows!

Average, Min and Max on an empty sequence of non-nullable values throw InvalidOperationException. Casting the value to a nullable type ((decimal?)o.Total) makes the result null instead.

Choosing a join style

Navigation Use first, joins for free
Join No relationship configured
LeftJoin Keep unmatched rows

Left join values are nullable

1 / 2

Stand-in: join and group

This stand-in is not EF Core, but Join, GroupBy, Count and Sum have the same meaning. Study how the left join keeps Grace, who has no orders.

C# Stand-in: inner join, left join and group

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Stand-in: aggregates on empty data

C# Stand-in: empty sequences and nullable casts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When do you not need an explicit Join in EF Core?

    Using o.Customer.Name in Where or Select makes EF Core add the join for you.
  2. 2. How does a Where placed after GroupBy translate?

    A filter on the group (for example on g.Sum) becomes a HAVING clause.
  3. 3. Why does MaxAsync(o => o.Total) throw on an empty table but MaxAsync(o => (decimal?)o.Total) returns null?

    An empty set has no maximum. A nullable return type can represent that as null.
  4. 4. Which grouping query is NOT translatable as one SQL statement?

    Returning the group elements needs the rows themselves; SQL GROUP BY returns one row per group.

Summary

  • Navigation properties give you joins for free; use Join only without a configured relationship.
  • Left joins keep unmatched rows; their values must be nullable (EF Core 10 adds LeftJoin).
  • GroupBy followed by a Select of the key and aggregates is one SQL GROUP BY; a Where after it is HAVING.
  • Cast to nullable types for Max, Min and Average on data that may be empty.

Next: Client vs Server Evaluation, Tracking and Global Filters