.NET & C# Development · Lesson 19 of 92

IEnumerable vs IQueryable — Stop Pulling 100k Rows You Don't Need

One interface runs your filter in memory. The other runs it in the database. Getting this wrong is one of the most common performance problems in .NET APIs — and it's completely invisible until your app handles real data.

The Core Difference

// IEnumerable — filter runs in memory (C#)
IEnumerable<Product> products = db.Products.ToList();  // ← ALL rows loaded here
var cheap = products.Where(p => p.Price < 100);        // ← filter in C#

// IQueryable — filter runs in the database (SQL)
IQueryable<Product> query = db.Products;               // ← no query yet
var cheap = query.Where(p => p.Price < 100);           // ← still no query
var result = cheap.ToList();                           // ← ONE query with WHERE clause

With IEnumerable: every row comes down the wire, then C# discards the ones you don't want.

With IQueryable: the filter becomes part of the SQL. Only matching rows leave the database.

What the SQL Actually Looks Like

// This IEnumerable code...
var products = db.Products.AsEnumerable().Where(p => p.Price < 100).ToList();

SQL

-- Generates this SQL (no WHERE):
SELECT id, name, price, stock, description, ... FROM products

// This IQueryable code...
var products = db.Products.Where(p => p.Price < 100).ToList();

SQL

-- Generates this SQL (WHERE clause included):
SELECT id, name, price, stock, description, ... FROM products
WHERE price < 100

On a table with 1 million rows and 500 matching, IEnumerable moves 1,000,000 rows out of the database. IQueryable moves 500.

IEnumerable in Detail

IEnumerable<T> is in System.Collections.Generic. It represents a sequence you can iterate over — in memory.

Any .Where(), .Select(), .OrderBy() etc. called on an IEnumerable are LINQ to Objects — they run as C# code on data that has already been fetched.

// When EF Core materialises a query (ToList, ToArray, FirstOrDefault, etc.)
// the result becomes IEnumerable — all in memory from here
List<Product> all = await db.Products.ToListAsync();   // fetched

// These run in C#, not in the DB:
var filtered = all.Where(p => p.Category == "Electronics");
var sorted   = all.OrderBy(p => p.Name);
var top10    = all.Take(10);  // takes 10 from the C# list, but ALL 1M rows are already loaded

When IEnumerable is the right choice:

You're working with in-memory collections (List, Array)
You've already fetched from the DB and need to process the result
You need LINQ operators that EF Core can't translate to SQL (string formatting, complex C# logic)

IQueryable in Detail

IQueryable<T> extends IEnumerable<T> and adds an Expression and a Provider. The provider (EF Core in this case) translates the expression tree into SQL at execution time.

Nothing hits the database until you materialize — ToList(), FirstOrDefault(), Count(), Any(), ToArray(), iterating in a foreach.

// Building the query — no DB call yet
IQueryable<Product> query = db.Products
    .Where(p => p.Category == "Electronics")
    .Where(p => p.Price < 500)
    .OrderBy(p => p.Name)
    .Take(10);

// ONE SQL call here:
var result = await query.ToListAsync();

SQL

SELECT TOP 10 id, name, price, ...
FROM products
WHERE category = 'Electronics' AND price < 500
ORDER BY name

All filters, sorting, and limiting are done in SQL. The application receives exactly the 10 rows it asked for.

When IQueryable is the right choice:

Querying a database via EF Core (or another ORM)
Building queries conditionally (add .Where() clauses based on user input)
Any situation where you want the database to do the work

The Repository Pattern Trap

This is where the mistake usually lives:

// ❌ Repository returns IEnumerable — forces full table load
public interface IProductRepository
{
    IEnumerable<Product> GetAll();  // ← this loads everything
}

// Service layer — filter runs in C#, on 1M rows
var expensive = _repo.GetAll().Where(p => p.Price > 1000).ToList();

Fix: return IQueryable from the repository so the caller can compose the query before execution.

// ✅ Repository returns IQueryable — query not executed yet
public interface IProductRepository
{
    IQueryable<Product> GetAll();
}

// Service layer — filter becomes part of the SQL
var expensive = await _repo.GetAll()
    .Where(p => p.Price > 1000)
    .ToListAsync();

There's a legitimate debate about whether repositories should expose IQueryable (it leaks the abstraction) or IEnumerable (it forces materialisation). The pragmatic answer for most teams: expose specific query methods that accept filter parameters, and use IQueryable internally.

// ✅ Best of both: specific methods, IQueryable internally, IEnumerable returned
public async Task<IReadOnlyList<Product>> GetExpensiveAsync(decimal minPrice)
{
    return await _db.Products
        .Where(p => p.Price >= minPrice)
        .AsNoTracking()
        .ToListAsync();
}

Conditional Query Building

IQueryable makes conditional filtering composable without string concatenation or multiple code paths:

// ❌ Old-school: separate queries per filter combination
if (categoryFilter != null && priceFilter != null) { ... }
else if (categoryFilter != null) { ... }
else if (priceFilter != null) { ... }
else { ... }

// ✅ Compose the IQueryable — one query, built conditionally
IQueryable<Product> query = db.Products.AsNoTracking();

if (!string.IsNullOrEmpty(request.Category))
    query = query.Where(p => p.Category == request.Category);

if (request.MinPrice.HasValue)
    query = query.Where(p => p.Price >= request.MinPrice.Value);

if (request.MaxPrice.HasValue)
    query = query.Where(p => p.Price <= request.MaxPrice.Value);

if (!string.IsNullOrEmpty(request.Search))
    query = query.Where(p => p.Name.Contains(request.Search));

// Single SQL executed here, with only the relevant WHERE clauses:
var results = await query
    .OrderBy(p => p.Name)
    .Skip((request.Page - 1) * request.PageSize)
    .Take(request.PageSize)
    .ToListAsync();

This generates one optimised SQL query regardless of which filters are active.

`AsEnumerable()` — Intentional Switch

Sometimes you need to switch from IQueryable to IEnumerable deliberately — when the LINQ operator you need can't be translated to SQL:

var results = await db.Products
    .Where(p => p.Category == "Electronics")   // runs in SQL ✅
    .AsNoTracking()
    .ToListAsync();                             // ← materialise here

// Post-process in memory — complex formatting EF can't translate
var formatted = results
    .Select(p => new ProductViewModel
    {
        Name = p.Name.ToTitleCase(),            // custom method — not translatable
        PriceFormatted = p.Price.ToString("C"), // culture-specific formatting
    })
    .ToList();

Fetch with IQueryable to get only the rows you need, then post-process as IEnumerable. This is the correct pattern when you need both worlds.

`AsNoTracking()` — Always Use for Reads

Unrelated to IEnumerable vs IQueryable, but always pair it with read-only IQueryable queries:

// ❌ Change tracking overhead on a read-only query
var products = await db.Products.Where(...).ToListAsync();

// ✅ 20-30% faster for read-only queries — no change tracker overhead
var products = await db.Products.AsNoTracking().Where(...).ToListAsync();

EF Core tracks every entity it loads so it can detect changes on SaveChanges. If you're not changing the data, this is wasted work.

Quick Reference

IEnumerable
  ├── Runs in: C# (in memory)
  ├── SQL: already executed before filter
  ├── Use for: in-memory collections, post-fetch processing
  └── Risk: loading entire table when you only need 10 rows

IQueryable
  ├── Runs in: database (SQL)
  ├── SQL: built from expression tree, executed on materialisation
  ├── Use for: EF Core queries, conditional filter building
  └── Risk: if you forget AsNoTracking(), change tracking adds overhead

AsEnumerable()    → intentionally switch to in-memory processing
AsNoTracking()    → skip change tracking for read-only queries
ToListAsync()     → execute the query (materialise)

The rule: let the database filter, sort, and paginate. Pull only the rows you'll actually use.

Generate Beautiful API Docs with OpenAPI & Scalar

Next Lesson

EF Core from Scratch — Map Your First Database Table