Querying for Scale

The difference between IQueryable and IEnumerable is the difference between a fast app and a crashed database. Understanding this is mandatory for any Senior Developer.

1. IEnumerable: The In-Memory Walker

When you use IEnumerable, the filtering happens Client-Side (in your app's RAM). If you query a table with 1 million rows, IEnumerable will pull all 1 million rows from the DB into your app and then filter them. **Result: OutOfMemoryException.**

2. IQueryable: The Source-Side Filter

When you use IQueryable, the LINQ query is NOT executed in C#. Instead, it's translated into SQL and executed on the Database Server. Only the matching records are sent over the network.

// Bad: Pulls all users, then filters in RAM
IEnumerable<User> users = _db.Users;
var results = users.Where(u => u.Age > 25); 

// Good: Sends 'SELECT * FROM Users WHERE Age > 25' to SQL
IQueryable<User> users = _db.Users;
var results = users.Where(u => u.Age > 25);
        

3. Architect Insight

Q: "When should I convert IQueryable to IEnumerable?"

Architect Answer: "Only convert when you need to perform an operation that the database cannot handle (like a custom C# helper method or complex string manipulation). Use `.ToList()` or `.AsEnumerable()` to force the execution, but only AFTER you have done all possible filtering on the DB side."