IEnumerable vs IQueryable and Deferred Execution – C# | Lesson 49

Published / updated5 Oct 2026

Module 8 · Lesson 49Intermediate40 minInterview:FMSA

Before this lesson: Generics: One PagedResult for Every Type – C#

Introduction

"What is the difference between IEnumerable and IQueryable?" is one of the most asked questions in C# interviews. Most answers are memorised sentences. In this lesson, you will see the difference, by counting exactly how many lines of a file a query reads, and when.

The key idea is deferred execution: writing a query does not run it. It runs only when something asks for the results. Understanding when that moment happens separates fast code from code that quietly reads the same data again and again.

By the end of this lesson, you will be able to:

• Explain what IEnumerable<T> is, and how foreach uses it.
• Write an iterator with yield return that streams data one item at a time.
• Explain deferred execution, and spot multiple enumeration.
• Use ToList() to run a query once and keep the results.
• Explain why IQueryable<T> can be translated into SQL, and why that matters.

Why this matters

In Module 9, Entity Framework Core returns IQueryable<T>. Filter it correctly, and SQL Server sends back only the rows you need. Filter it in the wrong place, and the whole table travels to your application first, then gets filtered in memory.

With 600,000 attendance rows, that mistake turns a 50-millisecond query into a page that takes seconds and uses hundreds of megabytes. The cause is always the same: not knowing when, and where, a query actually runs.

The concept

Example: IEnumerable, the interface behind foreach

IEnumerable<T> means "something you can walk through, one item at a time". It has one method that gives out an enumerator, which knows how to move to the next item. foreach uses it behind the scenes.

Arrays, lists, dictionaries, and sets all implement it, which is why foreach works on all of them. They form a family of interfaces, each adding more abilities:

Interface	Adds	Example type
IEnumerable<T>	Walk through items	Anything foreach accepts
IReadOnlyCollection<T>	Count	Read-only views
ICollection<T>	Add, Remove	HashSet<T>
IList<T>	Access by position	List<T>, arrays

Example: yield return

A method that returns IEnumerable<T> can hand back items one at a time with yield return. Each time the caller asks for the next item, the method runs until the next yield return, then pauses until it is asked again.

Think of a teacher reading the register aloud to find three absent students. She does not copy the whole register first; she reads one name, checks it, and moves on, stopping as soon as she has three. That is an iterator.

Example: deferred execution

LINQ methods such as Where and Take, covered fully in Lesson 50, do not run when you call them. They build a query: a plan, waiting to be walked through.

The plan runs when something needs the items: a foreach, Count(), First(), or ToList(). Until then, nothing is read. The expression m => m.MarksObtained > 95 is a lambda, a short inline function that Where applies to each mark; Lesson 51 explains lambdas in full.

Apply it to the SMS

Step 1: a streaming reader

DataFiles gets a ReadMarks method for the 12,012 marks, with a counter of lines read:

src/SchoolApp.Console/DataFiles.cs

using System.Globalization;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;

// Lesson 47: reads the Class 10 data exported from SchoolDb (the data folder).
// Module 9 replaces these files with real database queries.
static class DataFiles
{
    private static string PathOf(string fileName) => Path.Combine(AppContext.BaseDirectory, "data", fileName);

    // One Enrollment per line, linked to its Student and its Section in both directions.
    public static List<Enrollment> LoadClass10Enrollments()
    {
        List<Enrollment> enrollments = new List<Enrollment>();
        Dictionary<string, Section> sections = new Dictionary<string, Section>();
        bool isHeader = true;

        foreach (string line in File.ReadLines(PathOf("class10-students.csv")))
        {
            if (isHeader)
            {
                isHeader = false;
                continue;
            }

            // AdmissionNumber,FirstName,LastName,DateOfBirth,Gender,Section,RollNumber
            string[] f = line.Split(',');
            DateOnly dateOfBirth = DateOnly.ParseExact(f[3], "yyyy-MM-dd", CultureInfo.InvariantCulture);
            Student student = new Student(f[0], f[1], f[2], dateOfBirth, (Gender)f[4][0]);

            if (!sections.TryGetValue(f[5], out Section? section))
            {
                section = new Section { Name = f[5], ClassId = 10 };
                sections.Add(f[5], section);
            }

            Enrollment enrollment = new Enrollment
            {
                AcademicYearId = 2,
                RollNumber = short.Parse(f[6], CultureInfo.InvariantCulture),
                Student = student,
                Section = section
            };
            student.Enrollments.Add(enrollment);
            section.Enrollments.Add(enrollment);
            enrollments.Add(enrollment);
        }

        return enrollments;
    }

    // Lesson 49: counts the lines ReadMarks has really read, to make deferred execution visible.
    public static int MarkLinesRead { get; private set; }

    // Lesson 49: yield return hands back one mark at a time, reading the file only as far as needed.
    public static IEnumerable<MarkRecord> ReadMarks()
    {
        bool isHeader = true;
        foreach (string line in File.ReadLines(PathOf("class10-marks.csv")))
        {
            if (isHeader)
            {
                isHeader = false;
                continue;
            }

            MarkLinesRead++;

            // AdmissionNumber,Exam,Subject,MaxMarks,MarksObtained (empty when absent)
            string[] f = line.Split(',');
            decimal? obtained = f[4].Length == 0 ? null : decimal.Parse(f[4], CultureInfo.InvariantCulture);
            yield return new MarkRecord(f[0], f[1], f[2], decimal.Parse(f[3], CultureInfo.InvariantCulture), obtained);
        }
    }
}

// Lesson 49: one row of data/class10-marks.csv.
record MarkRecord(string AdmissionNumber, string Exam, string Subject, decimal MaxMarks, decimal? MarksObtained);

The counter is a static property, which Lesson 42 warned about for real data. Here it only measures a demo, in a single-user console app.

Step 2: the program

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs

// Lesson 49: IEnumerable versus IQueryable, on the 12,012 Class 10 marks.

// 1. Deferred execution: building the query reads nothing yet.
IEnumerable<MarkRecord> highMarks = DataFiles.ReadMarks().Where(m => m.MarksObtained > 95);
Console.WriteLine($"After building the query: {DataFiles.MarkLinesRead} lines read");

// 2. Asking for the first 3 reads only as far as needed.
foreach (MarkRecord mark in highMarks.Take(3))
{
    Console.WriteLine($"  {mark.AdmissionNumber} {mark.Exam}, {mark.Subject}: {mark.MarksObtained}");
}
Console.WriteLine($"After taking 3: {DataFiles.MarkLinesRead} lines read");

// 3. Counting needs every line, and each enumeration starts again from the top.
int count = highMarks.Count();
Console.WriteLine($"Marks above 95: {count}; lines read so far: {DataFiles.MarkLinesRead}");
int countAgain = highMarks.Count();
Console.WriteLine($"Counted again: {countAgain}; lines read so far: {DataFiles.MarkLinesRead}");

// 4. ToList runs the query once and keeps the results in memory.
List<MarkRecord> highMarksList = highMarks.ToList();
int before = DataFiles.MarkLinesRead;
int listCount = highMarksList.Count;
int listCountAgain = highMarksList.Count;
Console.WriteLine($"List: {listCount} and {listCountAgain} items; extra lines read while counting: {DataFiles.MarkLinesRead - before}");

// 5. IQueryable: the query is kept as an expression that a provider can translate, for example into SQL.
IQueryable<MarkRecord> mathsQuery = highMarksList.AsQueryable().Where(m => m.Subject == "Mathematics");
Console.WriteLine($"Expression: {mathsQuery.Expression}");
Console.WriteLine($"Mathematics marks above 95: {mathsQuery.Count()}");

Output

After building the query: 0 lines read
  ADM-2026-09004 Unit Test 1, Science: 100.00
  ADM-2026-09007 Unit Test 1, Social Studies: 99.00
  ADM-2026-09010 Unit Test 1, Hindi: 98.00
After taking 3: 71 lines read
Marks above 95: 736; lines read so far: 12083
Counted again: 736; lines read so far: 24095
List: 736 and 736 items; extra lines read while counting: 0
Expression: System.Collections.Generic.List`1[MarkRecord].Where(m => (m.Subject == "Mathematics"))
Mathematics marks above 95: 123

Both counts match SQL Server: a COUNT(*) with MarksObtained > 95 for Class 10 also gives 736, and 123 for Mathematics.

Example: zero lines, then 71

After highMarks was built, 0 lines had been read. The query existed only as a plan.

Take(3) then asked for three items. The reader went through the file only until the third mark above 95 appeared, the 71st mark in the file, and stopped.

The other 11,941 lines were never read. That is streaming: work proportional to what you need, not to the size of the data.

Example: multiple enumeration

Count() must see every mark, so it read all 12,012 lines. Then the same Count() ran again, and the reader went through the file again: 24,095 lines in total.

The variable highMarks does not hold results; it holds the plan. Every enumeration repeats the plan from the start.

Against a database, that means sending the same query twice. Code analysis tools warn about "possible multiple enumeration" for this reason.

Example: a query sees later changes

Because the plan runs late, it sees the data as it is at that moment, not when the query was written:

Example

List<decimal> marks = new List<decimal> { 92m, 85m };
IEnumerable<decimal> highMarks = marks.Where(m => m > 90);
marks.Add(95m);
Console.WriteLine(highMarks.Count());

It prints 2, not 1: the 95 added after the query was built is counted too. Usually that is what you want. When it is not, call ToList() at the moment the snapshot should be taken.

Example: ToList runs the query once

ToList() walked the plan one more time and stored the 736 results in a List. Counting the list twice read 0 extra lines, because the results now live in memory.

You need	Use
The results once, item by item	Enumerate the query directly, such as foreach
The results several times	ToList() once, then use the list
Only the first few	Take(n) or First() on the query: less work

ToList() is not free: it holds every result in memory. Use it when you will reuse the results, not by habit. For 736 marks that is nothing; for 600,000 attendance rows it matters.

Example: Any() versus Count() > 0

To ask "is there at least one mark above 95?", highMarks.Any() is better than highMarks.Count() > 0. Any stops at the first match, like Take(3) stopped after 71 marks. Count must walk through every one of the 12,012 lines before it can answer.

The same rule holds for databases: Any becomes a cheap EXISTS check, while Count makes the server count every matching row.

Example: IQueryable keeps the expression

AsQueryable() turned the list into an IQueryable<MarkRecord>. Printing .Expression shows the query as data: List...Where(m => (m.Subject == "Mathematics")).

That is the real difference. An IEnumerable query holds compiled code that runs in your program. An IQueryable query holds an expression tree: a description of the query that a provider can read and translate into something else.

For a list, the provider simply runs it in memory. In Module 9, EF Core's provider reads the same kind of expression and writes a SQL WHERE clause, so SQL Server does the filtering and returns only matching rows.

Example: IEnumerable versus IQueryable

	IEnumerable<T>	IQueryable<T>
Namespace	System.Collections.Generic	System.Linq
Query is held as	Compiled code (delegates)	An expression tree (data)
Runs	In your application's memory	Wherever the provider sends it, such as SQL Server
Filtering a database table	After all rows are loaded	In the database, before rows are sent
Typical source	Lists, arrays, files	EF Core DbSet<T>

The classic mistake in Module 9 will be calling AsEnumerable() or ToList() too early, before Where. The filter then runs in C#, after the whole table has crossed the network.

Common mistakes

1. Enumerating the same query several times. Each time repeats all the work. Fix: ToList() once when you need the results more than once.
2. ToList() before filtering a database query. The whole table is loaded. Fix: filter on the IQueryable first, then materialise.
3. Assuming a query ran when it was written. Errors and changes show up later, at enumeration. Fix: remember the plan runs only when results are requested.
4. Count() on an endless iterator. It never finishes. Fix: use Take or First.
5. Returning IQueryable from a repository by habit. Callers can build any query against your database. Fix: return lists or IReadOnlyList<T> unless composition is intended.

In real projects

From 16 years of .NET projects

A school dashboard took nine seconds to load and used a gigabyte of memory. The code read attendance with context.AttendanceRecords.ToList() and then filtered for today's date in C#.

Every attendance row the school had ever recorded crossed the network on every page load, just to keep a few hundred. Moving the Where before ToList() let SQL Server filter, and the dashboard loaded in under 200 milliseconds.

The two versions looked almost identical. The difference was one method call, in one position, and knowing where the query really ran.

Best practices

• Know when each query runs: at foreach, Count, First, ToList, and similar calls.
• Use yield return to stream large data instead of loading it all.
• Materialise with ToList() once when results are reused.
• Filter database queries while they are still IQueryable.
• Expose IReadOnlyList<T> from repositories unless callers truly need to compose queries.

Recap

• IEnumerable<T> is the interface behind every foreach.
• yield return streams items: Take(3) read only 71 of 12,012 lines.
• Queries are deferred: building one reads nothing until results are requested.
• Each enumeration repeats the work: two Count() calls read 24,095 lines.
• ToList() runs a query once and keeps the results in memory.
• IQueryable<T> holds an expression tree that a provider, such as EF Core, can translate to SQL.

Homework

Library module task: Write an iterator FineSchedule(dueOn) that yields, for each day after the due date, the date, the days late, and the fine so far, forever.

For The Jungle Book, due on 20 July 2026, print the first three days, then find the date when the fine first reaches 20.

A correct result looks like:

Output

2026-07-21: 1 days late, fine 2
2026-07-22: 2 days late, fine 4
2026-07-23: 3 days late, fine 6
The fine reaches 20 on 2026-07-30

Show the solution

library-assignment/049-library-yield.cs

// Library homework, Lesson 49: an endless fine schedule, made safe by deferred execution.
// Run with:  dotnet run library-assignment/049-library-yield.cs
DateOnly dueOn = new DateOnly(2026, 7, 20);   // The Jungle Book, issued to Advika

// The schedule never ends, but nothing runs until someone asks for items.
IEnumerable<FineDay> schedule = FineSchedule(dueOn);

foreach (FineDay day in schedule.Take(3))
{
    Console.WriteLine($"{day.Date:yyyy-MM-dd}: {day.DaysLate} days late, fine {day.Fine}");
}

FineDay reaches20 = schedule.First(d => d.Fine >= 20);
Console.WriteLine($"The fine reaches 20 on {reaches20.Date:yyyy-MM-dd}");

static IEnumerable<FineDay> FineSchedule(DateOnly dueOn)
{
    int daysLate = 1;
    while (true)   // endless: safe only because yield return hands back one day at a time
    {
        yield return new FineDay(dueOn.AddDays(daysLate), daysLate, daysLate * 2m);
        daysLate++;
    }
}

record FineDay(DateOnly Date, int DaysLate, decimal Fine);

The while (true) loop would freeze a normal method. In an iterator it is safe, because each yield return pauses the method until the caller asks for another day. Calling Count() on this schedule, however, would never finish.

Quiz

1. How many lines were read right after building highMarks with Where?

   1. 12,012
   2. 71
   3. 0
   4. 736
   Show answer

   Answer: C. 0 Building a query creates a plan; nothing runs until results are requested.

2. Why did the second Count() read the whole file again?

   1. Count is slow
   2. The variable holds the query plan, not the results
   3. The file changed
   4. A bug in yield return
   Show answer

   Answer: B. The variable holds the query plan, not the results Every enumeration repeats the plan from the start.

3. What does ToList() do?

   1. Builds a plan
   2. Runs the query once and stores the results in memory
   3. Sorts the items
   4. Translates to SQL
   Show answer

   Answer: B. Runs the query once and stores the results in memory Afterwards, reading the list repeats no work.

4. What does an IQueryable query hold that an IEnumerable query does not?

   1. More items
   2. An expression tree a provider can translate, for example into SQL
   3. A database connection
   4. A cache
   Show answer

   Answer: B. An expression tree a provider can translate, for example into SQL The expression is data, so EF Core can turn it into a WHERE clause.

5. With EF Core, where should Where come relative to ToList()?

   1. After
   2. Before
   3. It does not matter
   4. Never use Where
   Show answer

   Answer: B. Before Filter first so SQL Server returns only matching rows.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Advanced C# interview questions.

Fresher (0-2 yrs)

F What is IEnumerable<T>? concept

The interface for anything you can walk through one item at a time. It provides an enumerator, which foreach uses. Arrays, lists, dictionaries, and LINQ queries all implement it.

See the example · Permalink

F What does yield return do? concept

It hands back one item from a method that returns IEnumerable<T>, then pauses the method until the caller asks for the next item. Data is streamed: reading the marks file with Take(3) read only 71 of 12,012 lines.

See the example · Permalink

F What is deferred execution in LINQ? concept

Building a query with methods like Where does not run it; it creates a plan. The plan runs only when results are requested, by foreach, Count(), First(), ToList(), and similar calls.

See the example · Permalink

Mid (3-5 yrs)

M What is the difference between IEnumerable<T> and IQueryable<T>? concept

An IEnumerable query holds compiled code and runs in your application's memory. An IQueryable query holds an expression tree that a provider translates, for example EF Core into SQL, so filtering happens in the database before rows are sent.

See the example · Permalink

M What is multiple enumeration, and how do you avoid it? concept

Enumerating the same deferred query more than once repeats all its work each time; two Count() calls read the marks file twice. If the results are needed several times, call ToList() once and reuse the list.

See the example · Permalink

M Why prefer Any() over Count() > 0? coding

Any() stops at the first matching item; Count() must examine every item. Against a database, Any() becomes an EXISTS check while Count() counts every matching row.

See the example · Permalink

Senior (6-9 yrs)

S A dashboard calls context.AttendanceRecords.ToList() and then filters today's records in C#. It is slow and uses a lot of memory. Why, and what is the fix? scenario

ToList() runs the query immediately, so every attendance row is loaded into memory before filtering. Apply Where while the query is still IQueryable, then ToList(), so SQL Server returns only today's rows.

How to answer: Explain where the query runs before and after the fix, quantify the waste (all rows versus a few hundred), and mention checking the generated SQL or logs to confirm.

See the example · Permalink

S Should a repository return IQueryable<T>? design

Usually not. Returning IQueryable lets callers compose any query against the database, spreading data access logic and making performance unpredictable. Return materialized results such as IReadOnlyList<T>, and add specific methods for the queries you need.

How to answer: Present the trade-off: flexibility versus control and testability. Mention cases where exposing IQueryable is deliberate, such as OData or internal query builders.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you process millions of rows in .NET without running out of memory? design

Stream instead of materializing: read rows one at a time with an iterator, a data reader, or IAsyncEnumerable<T>, process each, and keep only aggregates. Filter and aggregate in the database where possible, and batch writes.

How to answer: Combine database-side work, streaming in the application, batching, and measuring memory. Mention that ToList on huge results is the usual cause of memory spikes.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-49-ienumerable-iqueryable (code repository coming soon)
• Files: src/SchoolApp.Console/DataFiles.cs, src/SchoolApp.Console/Program.cs, data/class10-marks.csv, library-assignment/049-library-yield.cs
• Video: coming soon

What's next

Where, Take, and Count were only a taste. Next: LINQ in full, to find toppers, averages, and pass rates in the 12,012 real marks with a few short, readable lines of C#. Lesson 50: LINQ

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.