IQueryable vs IEnumerable: The Line Between C# and SQL

IEnumerable vs IQueryable: The Difference Between C# and SQL

When filtering large data sets, the choice between IEnumerable and IQueryable can make a big difference in performance. Both use the same LINQ syntax, but their execution differs dramatically.

IEnumerable:

- Evaluates filtering in your application's memory using delegates

- Requires loading all data into memory first

- Becomes memory-intensive with millions of rows

- Example: dbContext.Products.Where(p => p.Price > 100).Where(p => p.Price > 100).Take(10).ToList();

IQueryable:

- Builds an expression tree that is translated into SQL by the database provider

- Filtering happens directly in the database, not in memory

- Ideal for large data sets and complex queries

- Example: dbContext.Products.Where(p => p.Price > 100).Where(p => p.Price > 100).ToList();

Expression Trees:

- IQueryable uses expression trees to represent queries as data structures

- The lambda expression .Where(p => p.Price > 100) becomes an expression tree

- Entity Framework walks the tree and generates SQL like:

SELECT * FROM Products WHERE Price > 100

When to use each:

- Use IQueryable whenever possible to push filtering to the database

- Switch to AsEnumerable() or ToList() only when you need C#-specific logic after filtering

- Returning IQueryable from repositories allows callers to add their own conditions

- Use IEnumerable (or materialized List) when the result set is final and no further filtering is needed

A common mistake is casting to IEnumerable too early, causing millions of rows to be loaded into memory before any filtering occurs. Keeping IQueryable for as long as possible ensures database-intensive filtering and efficient memory usage.