---
title: IEnumerable vs IList vs IQueryable
date: 2025-08-18
tags: dotnet, csharp, ef-core, linq
summary: Which collection interface to use where, and the one rule that keeps EF Core queries fast.
---

These three interfaces look similar, but they answer different questions. The short version:

- `IEnumerable<T>`: "I can loop over this." Works on data that is already in memory.
- `IList<T>`: "I can loop, index and change this." A list you own.
- `IQueryable<T>`: "This is a query that has not run yet." EF Core turns it into SQL.

## IEnumerable: in memory

LINQ on an `IEnumerable<T>` takes normal delegates (`Func<T, bool>`) and runs inside your process.

```csharp title="in memory"
IEnumerable<Order> orders = GetOrdersInMemory();

var vip = orders
    .Where(o => o.Customer.IsVip && o.Total > 1000)
    .Select(o => new { o.Id, o.Total });

foreach (var o in vip) // the filtering happens here, not above
    Console.WriteLine($"{o.Id}: {o.Total}");
```

The query is deferred. If you loop over it twice, the work runs twice. Call `ToList()` once if you need the result more than once.

## IList: when you need an index or changes

`IList<T>` adds `this[int]`, `Add`, `Insert` and `RemoveAt` on top of enumeration. Use it for a result you will sort, slice or edit.

If callers should not change what you return, use `IReadOnlyList<T>`. Accept `IEnumerable<T>` as a parameter when you only need to loop.

## IQueryable: the query that runs in the database

`DbSet<T>` is an `IQueryable<T>`. Its operators take expression trees (`Expression<Func<T, bool>>`) instead of delegates, so EF Core can read them and translate them to SQL. That is the reason `Where` has two signatures.

```csharp title="runs in the database"
var dtos = await db.Orders
    .Where(o => o.Customer.IsVip && o.Total > 1000)
    .OrderByDescending(o => o.Total)
    .Select(o => new OrderDto { Id = o.Id, Total = o.Total })
    .ToListAsync(ct); // one SQL query, runs here
```

Nothing hits the database until you materialize: `ToListAsync`, `FirstAsync`, `CountAsync` and so on.

## The one rule

> Let the database do the filtering and pick the columns. Call `ToListAsync()` as late as possible.

The rule has three parts:

1. Filter with `Where`, so the database throws away rows you don't need.
2. Select only the columns you need, so they are the only ones that cross the wire. In LINQ this is called projecting: `Select` picks the columns or reshapes them into a DTO.
3. Materialize last. `ToListAsync()` is the moment the SQL actually runs. After it, you work on a small list in memory.

Here is the same query, done wrong and then right:

```csharp title="wrong: filter in C#"
var orders = await db.Orders.ToListAsync();   // SELECT * FROM Orders
var result = orders
    .Where(o => o.Total > 1000)               // filtered in memory
    .Select(o => new OrderDto { Id = o.Id, Total = o.Total })
    .ToList();
```

```csharp title="right: filter in SQL"
var result = await db.Orders
    .Where(o => o.Total > 1000)
    .Select(o => new OrderDto { Id = o.Id, Total = o.Total })
    .ToListAsync();   // SELECT Id, Total FROM Orders WHERE Total > 1000
```

The first version loads every row and every column, then throws most of it away. The second asks the database for exactly what it needs.

A few more habits that go with it:

- `CountAsync()` on a query becomes `COUNT(*)`. Prefer `AnyAsync()` over `CountAsync() > 0` when you only need to know if something exists.
- Use `AsNoTracking()` for read-only queries. It skips the change tracker.

## When EF cannot translate your code

If a method has no SQL equivalent, EF Core throws instead of silently loading everything. Narrow down in SQL first, then switch to memory after `ToListAsync()` (or with `AsEnumerable()`):

```csharp title="SQL first, then memory"
var result = (await db.Orders
        .Include(o => o.Customer)        // load it, or it's null in memory
        .Where(o => o.Total > 1000)      // SQL
        .ToListAsync(ct))
    .Where(o => CustomIsVip(o.Customer)) // memory
    .ToList();
```

## Don't leak IQueryable

Do not return `IQueryable<T>` from a service or repository. The caller can add more filters, trigger extra queries, and now depends on what your EF provider can translate. Return DTOs or an `IReadOnlyList<T>` that is already materialized.

| Where | Use |
|---|---|
| Inside the data access layer | `IQueryable<T>` |
| Across a boundary (service, API) | `IReadOnlyList<T>` or DTOs |
| Business logic on loaded data | `IEnumerable<T>` |

## Two more EF Core things

- **Includes:** a big `Include` can produce a huge joined result. Try `AsSplitQuery()` and measure both.
- **Hot paths:** `EF.CompileAsyncQuery` skips the translation work for queries that run all the time.