IEnumerable<T> vs IQueryable<T>: When to use each

If you’ve worked with LINQ in C#, you’ve likely encountered IEnumerable<T> and IQueryable<T>. They both support LINQ queries, but their behavior differs significantly, especially when querying databases. Choosing the wrong one can lead to performance bottlenecks or bloated code. In this post, we’ll explore their differences, provide clear examples, and guide you on when to use each.

What Are They?

IEnumerable

• Definition: Represents an in-memory sequence of objects you can iterate over.

• Execution: Uses delegates (Func<T, bool>) to filter or transform data, executed by the CLR (Common Language Runtime).

• Typical Use: LINQ-to-Objects for in-memory collections like List<T> or arrays.

IQueryable

• Definition: Represents a query that can be executed against a query provider (e.g., Entity Framework Core for databases).

• Execution: Uses expression trees (Expression<Func<T, bool>>) that a provider translates into something like SQL.

• Typical Use: LINQ-to-Entities for database queries or remote data sources.

The core difference is where the query runs: IEnumerable<T> processes data in memory, while IQueryable<T> pushes processing to the data source (e.g., a database).

Working with In-Memory Collections

When querying in-memory data (like a List<T>), both IEnumerable<T> and IQueryable<T> execute in the CLR, so their differences are subtle.

IEnumerable Example

Filtering a list of users in memory:

var users = new List<User>
{
    new User { Name = "Alice", Age = 25 },
    new User { Name = "Bob", Age = 40 },
    new User { Name = "Charlie", Age = 35 }
};

// Filter users over 30 using IEnumerable<T>
IEnumerable<User> result = users.Where(u => u.Age > 30);

What’s happening?

• The Where clause uses a delegate (Func<User, bool>).

• The CLR iterates each element, applying the filter:

Func<User, bool> predicate = user => user.Age > 30;
foreach (var user in users)
{
    if (predicate(user))
    {
        yield return user; // Include if true
    }
}

Result: Returns Bob and Charlie, processed in memory.

IQueryable Example

Same filter, but with IQueryable<T>:

var users = new List<User>
{
    new User { Name = "Alice", Age = 25 },
    new User { Name = "Bob", Age = 40 },
    new User { Name = "Charlie", Age = 35 }
};

// Convert to IQueryable<T> and filter
IQueryable<User> result = users.AsQueryable().Where(u => u.Age > 30);

What’s happening?

• The Where clause builds an expression tree (Expression<Func<User, bool>>).

• Since no database provider is involved, LINQ-to-Objects evaluates the expression tree in memory, looping through the collection like IEnumerable<T>.

Outcome: For in-memory data, both interfaces perform similarly. Use IEnumerable<T> for simplicity unless you’re testing query provider behavior.

Working with Databases (Entity Framework Core)

When querying a database with Entity Framework Core, the choice between IEnumerable<T> and IQueryable<T> drastically affects performance. IEnumerable<T> processes data in memory, while IQueryable<T> lets the database handle the heavy lifting.

IEnumerable Example (Pulls All Data to Memory)

Suppose you’re querying a Users table:

// Load all users into memory
IEnumerable<User> users = dbContext.Users.AsEnumerable();

// Filter and sort in memory
var result = users
    .Where(u => u.IsActive && u.Age > 30)
    .OrderBy(u => u.LastName)
    .Take(2);

What’s happening?

• AsEnumerable() (or ToList()) forces the query to execute immediately, retrieving all rows from the Users table (e.g., SELECT * FROM Users).

• The CLR then applies Where, OrderBy, and Take to the in-memory collection.

• Problem: If the table has 1 million rows, all 1 million are loaded into memory before filtering or sorting. This is slow and resource-intensive.

Example SQL executed:

This line of code:

IEnumerable<User> users = dbContext.Users.AsEnumerable();

Will generate this SQL query:

SELECT * FROM Users;

• Downside: Network traffic spikes, memory usage balloons, and performance suffers.

⚠️ Important Note: Deferred Execution

If you don’t call .AsEnumerable() or .ToList(), the query is not executed immediately:

// IQueryable<User> (DbSet) upcasted to IEnumerable<User>
IEnumerable<User> users = dbContext.Users;

// Still part of the query pipeline
var result = users
    .Where(u => u.IsActive && u.Age > 30)
    .OrderBy(u => u.LastName)
    .Take(2);

What’s really happening:

• dbContext.Users is a DbSet<User>, which implements both IQueryable<User> and IEnumerable<User>.

• Simply assigning it to an IEnumerable<User> does not materialize the data.

• EF Core still sees the underlying IQueryable.

• When you chain .Where(), .OrderBy(), .Take(), the expression tree is preserved.

• EF Core translates the final query into SQL when you enumerate (e.g. foreach, .ToList()).

SQL Generated:

SELECT TOP(2) *
FROM Users
WHERE IsActive = 1 AND Age > 30
ORDER BY LastName;

Filtering, sorting, and limiting still happen in the database, not in memory.

Please read more about deferred execution here: Understanding Deferred Execution in LINQ with Entity Framework Core

IQueryable Example (Database-Optimized)

Same query, but using IQueryable<T>:

// Build query to execute in the database
IQueryable<User> usersQuery = dbContext.Users
    .Where(u => u.IsActive && u.Age > 30)
    .OrderBy(u => u.LastName)
    .Take(2);

// Execute when needed
var result = usersQuery.ToList();

What’s happening?

• The query builds an expression tree that EF Core translates into a single, optimized SQL query:

SELECT TOP(2) *
FROM Users
WHERE IsActive = 1 AND Age > 30
ORDER BY LastName;

• The database performs the filtering, sorting, and limiting, returning only the two matching rows.

• Benefit: Minimal data is transferred, and the database’s query engine optimizes the operation.

Outcome: IQueryable<T> is far more efficient for databases, as it minimizes data transfer and leverages the database’s capabilities.

Key Differences in Database Queries

• IEnumerable:

  • Pulls all data into memory before processing.

  • Filtering/sorting happens in the CLR, which is slow for large datasets.

  • Risk: Overloading memory and network with unnecessary data.

• IQueryable:

  • Translates LINQ operations into SQL, executed by the database.

  • Only retrieves the data you need.

  • Benefit: Faster, less memory usage, and scalable for large datasets.

A Real-World Analogy

• IEnumerable: You order every item from an online store, have it shipped to your house, then pick the ones you want. Wasteful!

• IQueryable: You filter your order online (e.g., “only blue shirts under $20”), and only those items are shipped. Efficient!

When to Use Each

Use IEnumerable

• In-memory collections: For List<T>, arrays, or cached data.

• Small datasets: When the data is already in memory and performance isn’t a concern.

• Example: Filtering a small list of cached items.

var cachedProducts = GetProductsFromCache(); // Returns List<Product>
var discounted = cachedProducts.Where(p => p.Price < 10);

Use IQueryable

• Database queries: For EF Core or other query providers (e.g., APIs).

• Large datasets: When you want the database to handle filtering, sorting, or paging.

• Example: Fetching paginated data from a database.

var activeUsers = dbContext.Users
    .Where(u => u.IsActive)
    .OrderBy(u => u.LastName)
    .Skip(10)
    .Take(5)
    .ToList();

Quick Comparison Table

Conclusions

• IEnumerable: Great for in-memory data like lists or arrays. Avoid for database queries unless the dataset is small.

• IQueryable: Ideal for databases or remote sources, pushing filtering and sorting to the data source for efficiency.

• Pro Tip: Be cautious with AsEnumerable() or ToList() in database queries—they force data into memory, negating IQueryable<T>’s benefits.

Choosing between IEnumerable<T> and IQueryable<T> depends on where your data lives. For in-memory work, IEnumerable<T> is simpler. For databases, IQueryable<T> is your go-to for performance and scalability. Next time you write a LINQ query, think about where the work should happen—client or server—and pick the right tool for the job.