All 18 LINQ questions
More in LINQ
Why can LINQ cause the N+1 query problem and how do you fix it? How does LINQ to Objects differ fundamentally from LINQ to Entities (Entity Framework), particularly regarding how the query is actually executed? How would you write a custom LINQ extension method that integrates seamlessly with the standard method-chaining style, like a 'DistinctBy' before it existed natively? How does LINQ's Aggregate() method work, and how would you use it to implement a custom reduction not covered by Sum()/Average()/Count()? How would you optimize a LINQ-to-Entities query to avoid the classic 'N+1 query' performance problem when accessing related navigation properties?
Advertisement
Back to results for “Collections”
C# › LINQ

How would you write an efficient LINQ query combining Join() with GroupBy() to replicate a SQL-style LEFT OUTER JOIN with aggregation?

Advanced

Answer

Use GroupJoin() (which pairs each element from the outer sequence with a GROUP of matching elements from the inner sequence, including an EMPTY group for no matches — mimicking a LEFT OUTER JOIN), then flatten and aggregate the grouped results as needed, unlike a regular Join() which behaves like an INNER JOIN and drops unmatched outer elements entirely.
var results = customers.GroupJoin(
    orders,
    customer => customer.Id,
    order => order.CustomerId,
    (customer, customerOrders) => new {
      customer.Name,
      OrderCount = customerOrders.Count() // 0 for customers with no matching orders, unlike an inner Join
    });
Real-world example Generating a report showing EVERY customer's order count, including customers with ZERO orders, mirroring a SQL LEFT JOIN with COUNT.

Common follow-ups: How does regular Join() (INNER JOIN semantics) behave differently for customers with no matching orders at all?

Collections
All 18 LINQ questions