Lesson 50 of 62Intermediate22 min

Deferred Execution, IEnumerable and IQueryable

By the end of this lesson

Explain when a query actually runs, and why this matters enormously with a database.

Everything in this module has quietly relied on one fact, and this lesson is where it gets stated properly: a LINQ query does not run when you write it. Where, Select, OrderBy, GroupBy and Join all return an object that describes work. The work happens later, when something asks the sequence for its items.

Asking for items is called enumerating: a foreach does it, and so does any operator that has to produce a final answer, such as Count, Sum, First, ToList or ToArray. Until one of those happens, the query has read nothing.

This is called deferred execution, or lazy evaluation. It has three consequences that surprise people, and one of them will cost you a great deal of memory and time the first time you meet it in an application that talks to a database.

The same query, three different answers

List<Employee> team = new List<Employee>
{
    new Employee("Asha Mehta", "Engineering", "Senior Engineer", 1_800_000m, new DateOnly(2019, 4, 1)),
    new Employee("Ravi Iyer",  "Engineering", "Engineer",        1_200_000m, new DateOnly(2021, 7, 12)),
};

// This line reads nothing and tests nothing. It builds a description.
IEnumerable<Employee> highEarners = team.Where(e => e.AnnualSalary > 1_000_000m);

Console.WriteLine(highEarners.Count());       // 2

team.Add(new Employee("Neha Kulkarni", "Engineering", "Engineering Manager", 2_400_000m, new DateOnly(2017, 1, 9)));

Console.WriteLine(highEarners.Count());       // 3 — same query object, different answer

// ToList runs the query now and keeps the result.
List<Employee> snapshot = highEarners.ToList();

team.Add(new Employee("Meera Nair", "Sales", "Sales Director", 2_700_000m, new DateOnly(2016, 3, 21)));

Console.WriteLine(highEarners.Count());       // 4 — re-runs against the changed list
Console.WriteLine(snapshot.Count);            // 3 — fixed when ToList ran

The Where line never touches an employee. Put a Console.WriteLine inside the lambda and run this: nothing prints until the first Count() call, and then it prints once per item, every single time the query is enumerated.
highEarners is not a result. It is a small object holding two things: the source to read and the predicate to apply. Every enumeration starts again from the source.
That is why the second Count() says 3. The query was defined before Neha existed, and it does not care — it reads team at the moment it runs.
ToList() is the moment the description becomes data. It enumerates once, copies the matching items into a new list, and that list stops changing.
The final two lines are the clearest picture of the difference: a live query and a snapshot, from the same definition, disagreeing by design. One is not better than the other; they answer different questions.

Watch out

A query you enumerate twice does the work twice

Nothing caches. If you pass a query to a method that counts it and then to a loop that prints it, the filtering, sorting and grouping all happen again. Over a list of eight, nobody notices. Over a query that reads a file or calls a database, this is the bug that makes a page slow for no visible reason.

The classic version of it: if (results.Any()) { foreach (var r in results) ... } — two enumerations of the same query, so two round trips to wherever the data lives. Call ToList() once and use the list twice.

It also makes results depend on timing. A query defined at the top of a method and enumerated at the bottom sees every change made in between, including changes made by code you did not write. That can be exactly what you want, and it can be a bug that only appears under load.

And modifying the source collection while a query over it is being enumerated throws InvalidOperationException, because the collection changed during iteration. Adding to a list between two enumerations is fine; adding to it inside the foreach is not.

The rule that keeps this manageable: keep queries deferred while you are still composing them, and materialise with ToList or ToArray at the point where the result is needed or crosses a boundary.

So far every source has been in memory, and the sequence type has been IEnumerable<T>. A filter over an IEnumerable<T> receives the lambda as compiled code — a delegate, a callable reference — and calls it once per item, in your process. There is no other option: the items are already here.

A database changes the picture. The data is somewhere else, it may be enormous, and the database is very good at filtering. You would rather the WHERE happened there and only the matching rows travelled to you. That requires sending the filter across, not running it locally, and code cannot be sent across a network — a description of it can.

IQueryable<T> is the interface for that. Its Where takes the lambda as an expression tree: a data structure describing the comparison rather than a compiled method. A library such as Entity Framework Core — the .NET library that maps C# classes to database tables — reads that tree and writes SQL from it. SQL being the language the database understands.

Both interfaces are used with identical-looking code. That is the convenience, and it is also the trap: the same chain of method calls can filter a million rows inside the database or drag a million rows across the network and filter them in your process, and the only difference on screen is the declared type of one variable.

The same operators, two very different execution stories:

	IEnumerable<T>	IQueryable<T>
What Where receives	Compiled code — a delegate	A description of the code — an expression tree
Where the filtering happens	In your process, item by item	Wherever the provider sends it, usually the database
What crosses the network	Everything the source hands over	Only the rows that match
Typical source	List, array, file lines, the result of another in-memory query	A DbSet from Entity Framework Core, or another IQueryable
What the lambda may contain	Any C# at all	Only what the provider can translate
How it fails	Quietly, by loading far more data than needed	Loudly, when an expression cannot be translated

One word, two wildly different queries

// Assume db is an Entity Framework Core context and the Employees table holds
// 250,000 rows, of which 5 have a salary above 5,000,000.

// BEFORE — the declared type is IEnumerable<Employee>.
IEnumerable<Employee> source = db.Employees;

List<Employee> loadedThenFiltered = source
    .Where(e => e.AnnualSalary > 5_000_000m)
    .ToList();

// SQL actually sent:
//   SELECT Name, Department, Role, AnnualSalary, JoiningDate FROM Employees
// 250,000 rows are transferred and turned into objects. Then C# discards 249,995.

// AFTER — the declared type is IQueryable<Employee>. Nothing else changed.
IQueryable<Employee> query = db.Employees;

List<Employee> filteredAtSource = query
    .Where(e => e.AnnualSalary > 5_000_000m)
    .ToList();

// SQL actually sent:
//   SELECT Name, Department, Role, AnnualSalary, JoiningDate FROM Employees
//   WHERE AnnualSalary > 5000000
// 5 rows are transferred.

db.Employees is a DbSet<Employee>, which implements both IQueryable<Employee> and IEnumerable<Employee>. Assigning it to an IEnumerable<Employee> variable does not convert anything at run time — but it does decide which Where the compiler binds to, because overload resolution happens at compile time from the declared type.
In the BEFORE version, Where is the in-memory one. It can only work on items, so enumerating it forces the whole table to be read first. The filter then runs in your process, correctly, on data that should never have been sent.
In the AFTER version, Where is the IQueryable one. It adds to the expression tree, and ToList() is the moment Entity Framework Core turns that tree into SQL and runs it. The comparison happens in the database.
Both versions return the same five employees, which is precisely why this survives code review and testing. On a development database with fifty rows the two are indistinguishable. In production the first one allocates 250,000 objects per request.
The same switch happens in three other ways, all easy to miss: calling AsEnumerable(), calling ToList() partway through a chain, and passing a DbSet to a method whose parameter is declared IEnumerable<Employee>. In each case every operator after that point runs in memory.

Common mistake

Common mistakes

Typing a method parameter or return value as IEnumerable<T> when callers will pass a database query. The method compiles, the results are correct, and the filtering silently moves out of the database. If a method is meant to add to a query, its parameter should be IQueryable<T>.

Calling ToList() early to feel safe, then filtering. That does fix the double-enumeration problem and replaces it with a worse one: the full result set now lives in memory.

Returning a deferred IQueryable<T> from a data-access class and enumerating it after the context has been disposed. The query tries to run against a connection that no longer exists, which surfaces as ObjectDisposedException a long way from the code that caused it.

Enumerating a query inside a loop. A query defined once and enumerated per row of an outer loop is a fresh database round trip each time — the N+1 problem, and the most common cause of a page that works fine with ten records and times out with a thousand.

Assuming everything translates. Where(e => MyHelper.IsSenior(e)) cannot become SQL, because the database has no idea what your method does. Entity Framework Core reports that it cannot translate the expression rather than silently fetching the table, so the fix is to express the rule in terms it can translate, or to fetch a deliberately narrowed set first and then filter in memory on purpose.

Summary

A LINQ query is a description of work; nothing runs until something enumerates it
Enumerating twice does the work twice, and changes to the source between definition and enumeration change the answer
ToList and ToArray run the query once and give you a result that stops changing
IEnumerable takes the lambda as compiled code and filters in your process; IQueryable takes it as an expression tree a provider can translate to SQL
Declaring a database query as IEnumerable, or calling AsEnumerable early, loads every row and filters in memory

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Try it yourself

Prove deferred execution to yourself. Build a list of three employees, define a Where query whose lambda prints the name it is testing, and then enumerate the query twice.

Count the lines printed. Then insert a ToList() and count again.

Show solution

Nothing prints when the query is defined, three lines print per enumeration, and the total is six. That is the whole idea made visible: the lambda is not a one-off, it is called again on every pass.

With ToList() in the chain, three lines print at that point and none afterwards, however many times you use the list. The work moved to a moment you chose.

This is the experiment worth remembering, because the same six-versus-three pattern is what a database sees. Two enumerations of a deferred query are two queries, and no warning appears anywhere.

List<Employee> team = employees.Take(3).ToList();

IEnumerable<Employee> query = team.Where(e =>
{
    Console.WriteLine($"testing {e.Name}");
    return e.AnnualSalary > 1_000_000m;
});

Console.WriteLine("query defined, nothing printed yet");

Console.WriteLine($"count: {query.Count()}");
Console.WriteLine($"count again: {query.Count()}");
// testing Asha Mehta / testing Ravi Iyer / testing Neha Kulkarni  -> count: 3
// testing Asha Mehta / testing Ravi Iyer / testing Neha Kulkarni  -> count again: 3
// Six "testing" lines in total.

List<Employee> materialised = query.ToList();   // three more "testing" lines
Console.WriteLine(materialised.Count);          // 3, and nothing further prints
Console.WriteLine(materialised.Count);          // 3, still silent

Think about it

A data-access class offers two versions of the same method: one returns IQueryable<Employee> straight from the context, the other returns List<Employee> after calling ToList().

What does each one give the caller, and what does each one take away?

Show solution

Returning IQueryable<Employee> lets the caller add to the query — another filter, an ordering, a page — and all of it still happens in the database. The cost is that the caller now holds something tied to a live database connection and to the shape of your tables, has to enumerate it before the context is disposed, and can compose an expression the provider cannot translate. The database concern has leaked out of the class whose job was to contain it.

Returning List<Employee> hands back a safe, finished result that cannot fail later and cannot surprise anybody. The cost is that every choice about filtering, ordering and paging had to be made inside the method, so callers who need something slightly different either get their own method or load more rows than they need and filter in memory.

Both are defensible and both are used in real systems. A common middle position: keep IQueryable inside the data-access layer where the context lives, and let each method take the parameters it needs — a department, a date range, a page number — so that filtering reaches the database without the query itself escaping. There is no answer that costs nothing; the decision is which cost you would rather carry.

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

You write IEnumerable<Employee> q = list.Where(e => e.AnnualSalary > 1_000_000m); then add a new high earner to list, then run foreach (var e in q). What does the loop see?

The items that matched at the moment the query was written, without the new oneThe matching items including the new one, because the filter runs during the foreachNothing, because the source changed after the query was definedAn InvalidOperationException, because the collection was modified

Saved in this browser only.