# 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:

```csharp
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:
    

```csharp
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>`:

```csharp
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:

```csharp
// 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:

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

Will generate this SQL query:

```sql
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**:

```csharp
// 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:

```csharp
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.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Prefer declaring <strong>IQueryable </strong>instead of <strong>IEnumerable </strong>if you want deferred execution and database-side filtering. <code>IQueryable users = dbContext.Users;</code> This makes it explicit to readers (and to yourself later) that the query will be composed and executed in the database, not in memory.</div>
</div>

Please read more about deferred execution here: [Understanding Deferred Execution in LINQ with Entity Framework Core](https://krisoncode.com/understanding-deferred-execution-in-linq-with-entity-framework-core)

### IQueryable Example (Database-Optimized)

Same query, but using `IQueryable<T>`:

```csharp
// 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:
    

```sql
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.
    

```csharp
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.
    

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

## Quick Comparison Table

| **Aspect** | **IEnumerable** | **IQueryable** |
| --- | --- | --- |
| **Definition** | In-memory sequence | Query against a provider (e.g., EF Core) |
| **Execution** | Delegates (`Func<T, bool>`) in CLR | Expression trees (`Expression<Func<T, bool>>`) |
| **In-Memory** | Runs in memory | Runs in memory (LINQ-to-Objects) |
| **Databases** | Loads all rows, filters in memory | Filters in database, returns minimal data |
| **Efficiency** | Inefficient for large remote datasets | Efficient for large remote datasets |
| **Use Case** | LINQ-to-Objects | LINQ-to-Entities, remote queries |

## 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.