IQueryable vs IEnumerable: The Line Between C# and SQL

You're filtering a million records. With one interface, the database does the work. With the other, your application drowns in memory. Same LINQ syntax. Wildly different execution.

Let's demystify where C# ends and SQL begins.

The Two Worlds

IEnumerable<Product> inMemory = products.Where(p => p.Price > 100);
IQueryable<Product> inDatabase = dbContext.Products.Where(p => p.Price > 100);

Same Where. Same lambda. Completely different animals.

• IEnumerable — evaluates in your app's memory using delegates
• IQueryable — builds an expression tree, translated to SQL by the database provider

The Million-Row Mistake

Watch this common anti-pattern:

public IEnumerable<Product> GetExpensive()
{
    return dbContext.Products;  // Oops — cast to IEnumerable
}

// Somewhere else
var results = GetExpensive()
    .Where(p => p.Price > 100)  // This runs IN MEMORY
    .Take(10)
    .ToList();

The Where looks innocent. But because we returned IEnumerable, the database returns all products first, then C# filters them. On a million rows, your server weeps.

The fix:

public IQueryable<Product> GetExpensive()
{
    return dbContext.Products;  // Keep IQueryable
}

// Now filtering happens at the database
var results = GetExpensive()
    .Where(p => p.Price > 100)  // SQL: WHERE Price > 100
    .Take(10)                    // SQL: TOP 10
    .ToList();

Expression Trees: The Magic Behind IQueryable

When you write .Where(p => p.Price > 100) on an IQueryable, the lambda isn't compiled to IL code. Instead, it becomes a data structure — an expression tree:

GreaterThan
├── MemberAccess: p.Price  
└── Constant: 100

Entity Framework walks this tree and generates:

SELECT * FROM Products WHERE Price > 100

Fun fact: the LINQ expression tree system was so powerful that other languages adopted it. F# got quotations, and JavaScript transpilers like TypeScript use similar AST concepts. Your LINQ query is code describing code.

The Boundary: Where IQueryable Fails

Not everything translates to SQL:

// This breaks at runtime
var results = dbContext.Products
    .Where(p => MyCustomMethod(p.Name))  // Can't translate to SQL!
    .ToList();

IQueryable doesn't know what MyCustomMethod does — it can only translate standard LINQ operators and expressions the provider understands.

Two solutions:

// Option 1: Filter in the database first, then apply custom logic
var results = dbContext.Products
    .Where(p => p.Price > 100)        // Runs in SQL
    .AsEnumerable()                    // Switch to in-memory
    .Where(p => MyCustomMethod(p.Name)) // Runs in C#
    .ToList();

// Option 2: Project to anonymous type first
var results = dbContext.Products
    .Where(p => p.Price > 100)
    .Select(p => new { p.Name, p.Price })  // Only fetch needed columns
    .AsEnumerable()
    .Where(x => MyCustomMethod(x.Name))
    .ToList();

The Rule of Thumb

1. Keep IQueryable as long as possible — push filtering to the database
2. Use AsEnumerable() or ToList() only when you need C#-specific logic
3. Return IQueryable from repository methods when callers need to add conditions
4. Return IEnumerable (or materialized List) when results are final

Quick Test: Where Does This Run?

dbContext.Products
    .Where(p => p.Price > 100)        // ?
    .OrderBy(p => p.Name)             // ?
    .ToList()                          // ?
    .Where(p => p.Stock > 0);         // ?

First three: SQL. Last one: C# (after ToList() materializes).

---

Next time we'll explore the dark art of projection — turning database rows into exactly the shape you need while minimizing data transfer. Hope to see you!