LINQ Fundamentals: IEnumerable vs IQueryable and Deferred Execution
Overview
LINQ (Language Integrated Query) is a powerful feature for querying collections. Understanding deferred execution and the difference between IEnumerable\<T\> and IQueryable\<T\> is crucial for writing efficient code.
IEnumerable<T> vs IQueryable<T>
Quick Comparison
| Aspect | IEnumerable<T> | IQueryable<T> |
|---|---|---|
| Purpose | In-memory collections | Database/remote queries |
| Execution | In-memory (LINQ to Objects) | Expression trees (LINQ to SQL/EF) |
| Where executed | Client side | Can be server side |
| Base namespace | System.Collections.Generic | System.Linq |
| Inherits from | - | IEnumerable<T> |
| Performance | Good for in-memory | Better for large datasets |
IEnumerable<T> - LINQ to Objects
List<int> numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
// IEnumerable - executes in memory
IEnumerable<int> evenNumbers = numbers.Where(n => n % 2 == 0);
// Entire collection is in memory, then filtered
foreach (var num in evenNumbers)
{
Console.WriteLine(num); // 2, 4, 6, 8, 10
}
IQueryable<T> - LINQ to SQL/EF
// IQueryable - builds expression tree, executes on database
using var context = new AppDbContext();
IQueryable<User> activeUsers = context.Users
.Where(u => u.IsActive)
.OrderBy(u => u.LastName);
// SQL is generated and executed on database:
// SELECT * FROM Users WHERE IsActive = 1 ORDER BY LastName
foreach (var user in activeUsers)
{
Console.WriteLine(user.Name);
}
Key Difference: Where Filtering Happens
// ❌ BAD - Loads ALL 1 million users into memory, then filters
IEnumerable<User> users = context.Users.AsEnumerable();
var activeUsers = users.Where(u => u.IsActive); // Filters in C# code!
// ✅ GOOD - Filtering happens in database
IQueryable<User> activeUsers = context.Users.Where(u => u.IsActive);
// SQL: SELECT * FROM Users WHERE IsActive = 1
Deferred Execution
What is Deferred Execution?
LINQ queries are not executed when defined - they're executed when enumerated.
List<int> numbers = [1, 2, 3, 4, 5];
// Query is DEFINED but NOT executed
var query = numbers.Where(n =>
{
Console.WriteLine($"Checking {n}");
return n > 2;
});
Console.WriteLine("Query defined"); // No "Checking X" messages yet!
// Query is EXECUTED here
foreach (var num in query)
{
Console.WriteLine($"Result: {num}");
}
// Output:
// Query defined
// Checking 1
// Checking 2
// Checking 3
// Result: 3
// Checking 4
// Result: 4
// Checking 5
// Result: 5
Deferred vs Immediate Execution
List<int> numbers = [1, 2, 3, 4, 5];
// ✅ Deferred - executed when enumerated
var deferred = numbers.Where(n => n > 2); // No execution
// ✅ Immediate - executed right now
var immediate = numbers.Where(n => n > 2).ToList(); // Executes immediately
var count = numbers.Count(n => n > 2); // Executes immediately
var first = numbers.First(n => n > 2); // Executes immediately
Deferred Execution Pitfall
// ❌ DANGEROUS - query sees modified collection!
var numbers = new List<int> { 1, 2, 3 };
var query = numbers.Where(n => n > 1); // Deferred
numbers.Add(4); // Modify collection after query
foreach (var num in query) // NOW query executes
{
Console.WriteLine(num); // 2, 3, 4 - includes the added 4!
}
// ✅ SAFE - capture snapshot
var numbers = new List<int> { 1, 2, 3 };
var query = numbers.Where(n => n > 1).ToList(); // Immediate execution
numbers.Add(4); // Won't affect query
foreach (var num in query)
{
Console.WriteLine(num); // 2, 3 - doesn't include 4
}
Query Syntax vs Method Syntax
Query Syntax (SQL-like)
var numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
var query = from n in numbers
where n % 2 == 0
orderby n descending
select n * 2;
// Result: 20, 16, 12, 8, 4
Method Syntax (Fluent)
var numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
var query = numbers
.Where(n => n % 2 == 0)
.OrderByDescending(n => n)
.Select(n => n * 2);
// Result: 20, 16, 12, 8, 4
When to Use Each
✅ Query syntax for:
- Complex queries with joins
- When SQL familiarity helps readability
- Multiple from clauses
✅ Method syntax for:
- Simple queries
- Chaining operations
- Using methods not available in query syntax (Take, Skip, etc.)
- Most modern C# code (industry preference)
Common LINQ Operations
Filtering
var numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
// Where - filter elements
var evens = numbers.Where(n => n % 2 == 0);
// OfType - filter by type
object[] mixed = [1, "hello", 2, "world", 3];
var strings = mixed.OfType<string>(); // ["hello", "world"]
Projection
var users = new List<User>
{
new User { Id = 1, Name = "Alice", Age = 30 },
new User { Id = 2, Name = "Bob", Age = 25 }
};
// Select - transform elements
var names = users.Select(u => u.Name); // ["Alice", "Bob"]
// Select - project to anonymous type
var userInfo = users.Select(u => new { u.Name, u.Age });
// SelectMany - flatten nested collections
var sentences = new List<string> { "Hello world", "Goodbye world" };
var words = sentences.SelectMany(s => s.Split(' '));
// ["Hello", "world", "Goodbye", "world"]
Ordering
var users = GetUsers();
// Single sort
var sorted = users.OrderBy(u => u.Age);
var sortedDesc = users.OrderByDescending(u => u.Age);
// Multiple sorts (ThenBy)
var multiSort = users
.OrderBy(u => u.LastName)
.ThenBy(u => u.FirstName)
.ThenByDescending(u => u.Age);
Aggregation
var numbers = new List<int> { 1, 2, 3, 4, 5 };
int count = numbers.Count(); // 5
int sum = numbers.Sum(); // 15
double average = numbers.Average(); // 3.0
int min = numbers.Min(); // 1
int max = numbers.Max(); // 5
// Aggregate - custom accumulation
int product = numbers.Aggregate((acc, n) => acc * n); // 120
Element Operations
var numbers = new List<int> { 1, 2, 3, 4, 5 };
// First - first element (throws if empty)
int first = numbers.First(); // 1
int firstEven = numbers.First(n => n % 2 == 0); // 2
// FirstOrDefault - returns default if empty
int firstOrDefault = numbers.FirstOrDefault(); // 1
int noMatch = numbers.FirstOrDefault(n => n > 10); // 0
// Single - exactly one element (throws if zero or multiple)
int single = new List<int> { 42 }.Single(); // 42
// Last, LastOrDefault - from end
int last = numbers.Last(); // 5
Set Operations
var list1 = new List<int> { 1, 2, 3, 4, 5 };
var list2 = new List<int> { 4, 5, 6, 7, 8 };
// Distinct - remove duplicates
var withDupes = new List<int> { 1, 2, 2, 3, 3, 3 };
var unique = withDupes.Distinct(); // [1, 2, 3]
// Union - all unique elements from both
var union = list1.Union(list2); // [1, 2, 3, 4, 5, 6, 7, 8]
// Intersect - common elements
var intersect = list1.Intersect(list2); // [4, 5]
// Except - in first but not second
var except = list1.Except(list2); // [1, 2, 3]
Partitioning
var numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
// Take - first N elements
var first3 = numbers.Take(3); // [1, 2, 3]
// Skip - skip first N elements
var skip3 = numbers.Skip(3); // [4, 5, 6, 7, 8, 9, 10]
// Pagination
int pageSize = 3;
int pageNumber = 2;
var page = numbers
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize); // [4, 5, 6]
// TakeWhile / SkipWhile - based on condition
var takeWhile = numbers.TakeWhile(n => n < 5); // [1, 2, 3, 4]
var skipWhile = numbers.SkipWhile(n => n < 5); // [5, 6, 7, 8, 9, 10]
Joins
var users = new List<User>
{
new User { Id = 1, Name = "Alice" },
new User { Id = 2, Name = "Bob" }
};
var orders = new List<Order>
{
new Order { UserId = 1, Product = "Laptop" },
new Order { UserId = 1, Product = "Mouse" },
new Order { UserId = 2, Product = "Keyboard" }
};
// Inner join
var userOrders = users.Join(
orders,
user => user.Id,
order => order.UserId,
(user, order) => new { user.Name, order.Product }
);
// Result:
// { Name = "Alice", Product = "Laptop" }
// { Name = "Alice", Product = "Mouse" }
// { Name = "Bob", Product = "Keyboard" }
// Group join (left outer join)
var usersWithOrders = users.GroupJoin(
orders,
user => user.Id,
order => order.UserId,
(user, userOrders) => new { user.Name, Orders = userOrders }
);
Performance Tips
1. Use IQueryable for Databases
// ❌ BAD - Loads entire table, filters in memory
var activeUsers = context.Users.AsEnumerable()
.Where(u => u.IsActive)
.ToList();
// ✅ GOOD - Filters in database
var activeUsers = context.Users
.Where(u => u.IsActive)
.ToList();
2. Avoid Multiple Enumerations
// ❌ BAD - query executed multiple times
var query = numbers.Where(n => n > 5); // Deferred
int count = query.Count(); // Executes
var first = query.First(); // Executes again!
var list = query.ToList(); // Executes again!
// ✅ GOOD - execute once, cache result
var list = numbers.Where(n => n > 5).ToList();
int count = list.Count;
var first = list.First();
3. Use Any() Instead of Count() for Existence Checks
// ❌ BAD - counts ALL elements
if (users.Count() > 0)
{
// ...
}
// ✅ GOOD - stops at first element
if (users.Any())
{
// ...
}
4. Filter Before Projecting
// ❌ BAD - projects all, then filters
var result = users
.Select(u => new UserDto { Name = u.Name, Age = u.Age })
.Where(dto => dto.Age > 18);
// ✅ GOOD - filters first, then projects
var result = users
.Where(u => u.Age > 18)
.Select(u => new UserDto { Name = u.Name, Age = u.Age });
Interview Questions
Q: What's the difference between IEnumerable<T> and IQueryable<T>?
A: IEnumerable\<T\> is for in-memory collections (LINQ to Objects), executing queries in C# code. IQueryable\<T\> is for remote data sources like databases, converting queries to expression trees that can be translated to SQL or other query languages. With IQueryable, filtering and operations can happen on the server before data is loaded.
Q: What is deferred execution and why does it matter?
A: Deferred execution means LINQ queries aren't executed when defined, but when enumerated (foreach, ToList(), etc.). This matters because: 1) The query sees the collection's current state, not when defined, 2) Queries can be built up incrementally, 3) You can avoid executing expensive queries if they're never used.
Q: When would you use ToList() or ToArray()?
A: Use when you need to: 1) Force immediate execution to capture a snapshot, 2) Enumerate multiple times without re-execution, 3) Get a Count without triggering re-enumeration, 4) Pass a materialized collection to methods that don't accept IEnumerable.
Q: Why is Any() better than Count() > 0?
A: Any() short-circuits and stops at the first element, while Count() must enumerate the entire collection. For IQueryable, Any() generates SELECT TOP 1 or EXISTS, while Count() generates SELECT COUNT(*).
Q: What's the performance difference between Where().Select() and Select().Where()?
A: Where().Select() is better because it filters first, reducing the number of elements that need projection. This is especially important with IQueryable - filtering before projection means less data transfer from the database.
Practice Exercises
- Convert a complex SQL query to LINQ
- Demonstrate deferred vs immediate execution with timing
- Show the difference between IEnumerable and IQueryable with Entity Framework
- Implement pagination using Skip() and Take()
- Write a query that demonstrates the pitfall of deferred execution with collection modification
Related Concepts
- Delegates and Lambdas
- Async LINQ with IAsyncEnumerable
- Expression Trees and how IQueryable works