Skip to content

Intermediate · Collections, generics and LINQ · topic 25 of 43 · 35 min

LINQ in depth: grouping, joins, deferred execution, IQueryable

Explanation

GroupBy puts items into groups by a key (orders by city), and Join matches items from two collections by a shared key (orders with customers). Most LINQ queries use deferred execution: they do not run when you write them, but each time you loop over them, so ToList() is used to run once and keep the results. IEnumerable<T> queries run in your app's memory, while IQueryable<T> queries (used by Entity Framework) are translated to SQL and run in the database.

GroupBy: totals per category

csharp
record Order(int Id, string City, decimal Amount, int CustomerId);

var orders = new List<Order>
{
    new(1, "Pune", 500m, 10), new(2, "Delhi", 1200m, 11),
    new(3, "Pune", 300m, 12), new(4, "Delhi", 800m, 10),
    new(5, "Goa", 450m, 11)
};

var byCity = orders
    .GroupBy(o => o.City)
    .Select(g => new { City = g.Key, Orders = g.Count(), Total = g.Sum(o => o.Amount) });

foreach (var c in byCity)
    Console.WriteLine($"{c.City}: {c.Orders} orders, Rs {c.Total}");
Shows Pune: 2 orders, Rs 800; Delhi: 2 orders, Rs 2000; Goa: 1 orders, Rs 450. Each group has a Key and contains its items. (As before, in a real Program.cs the records go at the bottom.)

GroupBy returns a sequence of groups. Each group is an IGrouping<TKey, TElement>: a Key plus the items in that group. You usually follow it with Select to make one summary row per group, exactly like GROUP BY in SQL.

Join: matching two collections

csharp
record Customer(int Id, string Name);
var customers = new List<Customer> { new(10, "Asha"), new(11, "Ravi"), new(12, "Meena") };

var lines = orders.Join(customers,
    o => o.CustomerId,           // key from orders
    c => c.Id,                   // key from customers
    (o, c) => $"#{o.Id} {c.Name} Rs {o.Amount}");

foreach (var l in lines) Console.WriteLine(l);

var same =
    from o in orders
    join c in customers on o.CustomerId equals c.Id
    select new { o.Id, c.Name };
Join pairs every order with the customer whose Id matches. Query syntax (equals, not ==) is often easier to read for joins.

SelectMany: flattening

csharp
var classes = new[]
{
    new { Section = "A", Names = new[] { "Asha", "Ravi" } },
    new { Section = "B", Names = new[] { "Meena" } }
};
var everyone = classes.SelectMany(c => c.Names);
Console.WriteLine(string.Join(", ", everyone));   // Asha, Ravi, Meena
SelectMany turns a list of lists into one flat list.

Deferred execution: queries are recipes

A LINQ query like Where or Select does not run when you write it. It is a recipe that runs when someone asks for the results: a foreach, ToList, Count, First and so on. This is called deferred (delayed) execution. It means the query sees the data as it is at that moment, and it runs again every time you enumerate it.

csharp
var nums = new List<int> { 1, 2, 3 };
var big = nums.Where(n => n > 1);   // nothing runs yet
nums.Add(4);
Console.WriteLine(big.Count());     // 3: the query ran now and saw 2, 3, 4
nums.Add(5);
Console.WriteLine(big.Count());     // 4: it ran again
Each Count() re-runs the whole query.
Think of it like this: A deferred query is like a recipe card for chai, not a cup of chai. Writing the card costs nothing. Each time someone asks for chai, you make it fresh from the card, using whatever milk is in the fridge now. ToList() is making one big pot and keeping it.
Deferred (returns a sequence)Immediate (runs now)
Where, Select, OrderBy, GroupBy, Join, Skip, Take, DistinctToList, ToArray, ToDictionary, Count, Sum, Average, Min, Max, First, Single, Any, All
Careful: Multiple enumeration: if a query is expensive (reads a file, calls a database) and you loop over it twice, the work happens twice. Call ToList() once and reuse the list.

IEnumerable<T> versus IQueryable<T>

IEnumerable<T> is for data already in your program's memory. Its LINQ methods take ordinary delegates (compiled functions) and run them in your app. IQueryable<T> is for data somewhere else, usually a database. Its LINQ methods take expression trees: a data description of your lambda that a provider such as Entity Framework Core reads and translates to SQL. The filtering then happens inside the database, and only matching rows travel over the network.

csharp
// db.Orders is an IQueryable<Order> from Entity Framework Core (module 8)
var bigOrders = db.Orders
    .Where(o => o.Amount > 1000)     // becomes WHERE Amount > 1000 in SQL
    .OrderBy(o => o.Id)
    .ToList();                       // runs ONE SQL query, returns only matching rows

var slow = db.Orders
    .ToList()                        // loads EVERY order into memory first!
    .Where(o => o.Amount > 1000);    // then filters in C#
The second version works, but on a table with crores of rows it can crash the app or make it very slow. Keep filters on the IQueryable before ToList.
sql
SELECT ... FROM Orders WHERE Amount > 1000 ORDER BY Id
Roughly the SQL that EF Core produces for the first query.

Common mistakes and fixes

  • Calling ToList() or AsEnumerable() too early on a database query: everything is loaded into memory. Filter, sort and page first.
  • Using a C# method EF Core cannot translate inside an IQueryable Where (like your own helper method): EF Core throws an error saying the query could not be translated. Rewrite with simple expressions or filter after loading a small result.
  • Surprised that a query result changed: deferred execution saw new data. Use ToList() to take a snapshot.
  • Forgetting that GroupBy keys are case-sensitive for strings: "pune" and "Pune" become two groups. Normalise first.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

LINQ in depth: grouping, joins, deferred execution, IQueryable · 1/7

LINQ in depth

In this lesson you will go deeper into LINQ: grouping, joining, and how queries really run.

Prefer a video by a teacher? Find free videos on “C# LINQ GroupBy Join deferred execution IEnumerable vs IQueryable” on YouTube ↗

In real life: A city-wise sales dashboard for a pizza chain

A pizza chain's head office in Mumbai wants, for each city, the number of orders, total sales and the best customer. LINQ grouping and joining build this report from the day's data in a few lines.

csharp
var report = orders
    .GroupBy(o => o.City)
    .Select(g => new
    {
        City = g.Key,
        Sales = g.Sum(o => o.Amount),
        TopCustomerId = g.GroupBy(o => o.CustomerId)
                         .OrderByDescending(cg => cg.Sum(o => o.Amount))
                         .First().Key
    })
    .Join(customers, r => r.TopCustomerId, c => c.Id,
          (r, c) => $"{r.City}: Rs {r.Sales}, best customer {c.Name}")
    .ToList();

report.ForEach(Console.WriteLine);
Using the orders and customers above, shows Pune: Rs 800, best customer Asha; Delhi: Rs 2000, best customer Ravi; Goa: Rs 450, best customer Ravi.

Practice (0/4 done)

Task 1 · Question

var list = new List<int> { 5, 10 }; var q = list.Select(x => x * 2); list.Add(20); Console.WriteLine(q.Sum()); What is printed?

Task 2 · Question

Which of these runs a query immediately?

Task 3 · Question

string[] w = { "apple", "avocado", "banana" }; How many groups does w.GroupBy(x => x[0]) make?

Task 4 · Practice

You have records Sale(string Product, string Category, int Qty, decimal Price). Write one LINQ query that returns, for each category, the category name, total quantity and revenue (Qty * Price), sorted by revenue from highest to lowest. Explain in one line why you would keep the Where/GroupBy before ToList if the data came from a database.

Quick quiz

Question 1

1.What does each group from GroupBy have?

Question 2

2.What is deferred execution?

Question 3

3.Why is db.Orders.ToList().Where(...) usually bad?

Question 4

4.IQueryable<T> LINQ methods receive lambdas as...

Question 5

5.In query syntax join, which keyword compares the keys?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

What does each group from GroupBy have?

Answer: A Key and the items in that group

IGrouping<TKey, TElement> has Key and is itself a sequence of items.

What is deferred execution?

Answer: A query runs only when its results are enumerated

The query is a recipe until enumerated.

Why is db.Orders.ToList().Where(...) usually bad?

Answer: It loads all rows into memory before filtering

Filter on the IQueryable so the database does the work.

IQueryable<T> LINQ methods receive lambdas as...

Answer: Expression trees that a provider can translate (e.g. to SQL)

Expression trees describe the code so EF Core can translate it.

In query syntax join, which keyword compares the keys?

Answer: equals

join ... on a equals b.

Which LINQ method filters items?Related

Answer: Where

Where keeps items for which the lambda returns true.

From LINQ basics: query data in a few lines

orders JOIN order_items: what does one row of the result stand for?Related

Answer: One order line

The grain becomes the more detailed table, order_items.

From Joining three or more tables

What does df.groupby("city")["amount"].sum() return?Related

Answer: The total amount for each city

Split by city, sum each group, combine into one value per city.

From Grouping, joining and reshaping

When does a deferred call run?Related

Answer: When the surrounding function returns

Deferred calls run as the function returns, including during a panic.

From defer: clean up at the end

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Exceptions: try, catch, finally and custom exceptions →

Next steps

  1. This is the last topic of “Collections, generics and LINQ”. The module exam comes next.
  2. Module 6 exam: Collections, generics and LINQ · 14 questions · 25 min, without help. 4 topics of this module still to finish first.
  3. Then the next module, Exceptions, delegates, events and async. 43 topics left in the course before the final exam.

← Previous: LINQ basics: query data in a few lines