IEnumerable vs IQueryable

🚀 C# Tip — IEnumerable vs IQueryable (The Difference That Actually Matters)

IEnumerable and IQueryable look similar.
They’re not.

Choosing the wrong one can lead to:

• Performance issues
• Loading way too much data
• Queries that work in dev and explode in prod

Let’s clarify this once and for all.

🧠 The Core Difference

• IEnumerable<T> → In-memory iteration
• IQueryable<T> → Query definition (deferred, remote execution)

That distinction drives everything else.

🧩 IEnumerable — In-Memory Execution

IEnumerable<Order> orders = GetOrders();

var expensive = orders
    .Where(o => o.Total > 100)
    .ToList();

What really happens:

• Data is already in memory
• LINQ runs inside your process
• Filtering happens after data is loaded

✅ When to use IEnumerable

• Collections already in memory
• Small datasets
• Business logic
• APIs returning final results

⚠️ Risks

• Accidental full-table loads
• Hidden performance costs
• Late discovery of inefficiencies

🧩 IQueryable — Query Translation

IQueryable<Order> orders = context.Orders;

var expensive = orders
    .Where(o => o.Total > 100)
    .ToList();

What really happens:

• LINQ builds an expression tree
• Provider (EF Core, LINQ-to-SQL) translates it
• Filtering runs in the database

✅ When to use IQueryable

• Data access layer
• EF Core queries
• OData / dynamic filtering
• Server-side paging, sorting, projection

⚠️ Risks

• Leaking IQueryable outside repositories
• Runtime query translation errors
• Hidden N+1 queries
• Security & performance surprises

⚖️ Side-by-Side Comparison

🧠 The Most Common Mistake

❌ Returning IQueryable from repositories:

public IQueryable<Order> GetOrders()
{
    return _context.Orders;
}

Why this is dangerous:

• Leaks persistence concerns
• Couples callers to EF Core
• Makes performance unpredictable

✅ Better:

public Task<List<Order>> GetOrdersAsync()
{
    return _context.Orders
        .Where(o => o.IsActive)
        .ToListAsync();
}

🏗️ A Clean Rule of Thumb

• Use IQueryable to build queries
• Use IEnumerable to consume data

Or even simpler:

IQueryable belongs to the infrastructure layer
IEnumerable belongs everywhere else

⚠️ Extra Gotchas (Real-World)

• Calling .AsEnumerable() switches execution to memory
• Mixing IQueryable + custom C# methods breaks translation
• IQueryable errors show up at runtime, not compile time
• Logging SQL is essential when using IQueryable

🎯 Final Takeaway

IQueryable is powerful — and dangerous.
IEnumerable is safe — and sometimes expensive.

Choosing between them is not a syntax choice.
It’s a data flow and responsibility choice.

Most performance bugs start with
“It was just an IQueryable…”

#dotnet #csharp #linq #efcore #performance #softwarearchitecture #cleancode