IQueryable vs IEnumerable

Analogy categoryAnalogies

IEnumerable<T> and IQueryable<T> both represent a sequence you can iterate, and both support the same LINQ operators, but they execute in fundamentally different ways. IEnumerable<T>'s Where takes a compiled Func<T,bool> delegate and runs it directly against objects already in memory — this is LINQ to Objects. IQueryable<T>'s Where takes an Expression<Func<T,bool>>, an expression tree describing the logic as data, which a query provider (like Entity Framework Core's SQL Server provider) walks and translates into an equivalent SQL query executed on the database server.

🏏

Cricket analogy: Like the difference between a player physically fielding every ball hit near them (IEnumerable, all-in-memory) versus radioing instructions to a specialist reserve fielder positioned exactly where needed (IQueryable, translated and executed elsewhere), both interfaces represent a sequence, but IQueryable<T> builds an expression tree a provider translates before execution.

IEnumerable<T> in Practice

When you query an in-memory List<T> or array, you're using IEnumerable<T>. Its Where compiles your lambda into a Func<T,bool> delegate and calls it directly against each element, one at a time, with no intermediate representation. This is fast for local data but means, if you were to somehow apply it to a database-backed source, it would require the entire table to already be loaded into memory first — there is no mechanism to push the filter down to the server.

🏏

Cricket analogy: Like a scorer manually flipping through a physical scorebook page by page to find every six hit, IEnumerable<T>'s Where takes a compiled Func<T,bool> delegate and tests each in-memory element directly, one at a time, with no translation step involved.

IQueryable<T> in Practice

When you query a DbSet<T> in Entity Framework Core, you're working with IQueryable<T>. Its Where compiles your lambda into an Expression<Func<T,bool>> — an expression tree that represents the code as data rather than executable instructions. EF Core's query provider walks that tree and generates a SQL WHERE clause, so the filtering happens on the database server, and only the matching rows are transferred over the network into your application's memory.

🏏

Cricket analogy: Like radioing a request to the ground's official scorer, who queries the ICC's central database for every century scored by Virat Kohli against Australia, IQueryable<T>'s Where builds an Expression<Func<T,bool>> that Entity Framework's provider translates into a SQL WHERE clause executed on the database server.

The Classic Pitfall

The most common mistake is converting an IQueryable<T> to IEnumerable<T> too early — by calling .ToList(), .AsEnumerable(), or by using a method the provider can't translate — before applying your filters. Doing so pulls the entire table across the network into memory, and everything after that point runs as slower, memory-hungry LINQ to Objects instead of an efficient server-side SQL query. Calling an unsupported method inside a query provider expression can also throw a runtime exception since the provider has no SQL equivalent to translate it to.

🏏

Cricket analogy: Like a scout requesting the entire international match database dumped onto a USB drive just to manually search for one player's centuries at home, calling .AsEnumerable() or .ToList() on an IQueryable before applying Where silently pulls the entire table into memory first, then filters locally, defeating server-side filtering.

csharp

// Good: filter is translated to SQL and runs on the database server
var bigOrders = dbContext.Orders
    .Where(o => o.Total > 1000)
    .OrderByDescending(o => o.Total)
    .Take(10)
    .ToList(); // materializes AFTER filtering

// Bad: pulls the ENTIRE Orders table into memory first
var allOrdersInMemory = dbContext.Orders.AsEnumerable();
var bigOrdersSlow = allOrdersInMemory
    .Where(o => o.Total > 1000) // now running as LINQ to Objects, client-side
    .OrderByDescending(o => o.Total)
    .Take(10)
    .ToList();

Calling .ToList() or .AsEnumerable() on an EF Core IQueryable<T> before applying Where/OrderBy/Take pulls the entire table into application memory and switches all subsequent operators to slower, client-side LINQ to Objects. Always apply your filters, sorts, and paging while the query is still IQueryable<T>, and materialize with .ToList() last.

IEnumerable<T> executes queries in-process using compiled Func<T,bool> delegates — this is LINQ to Objects.
IQueryable<T> builds Expression<Func<T,bool>> expression trees that a provider (like EF Core) translates into SQL.
IQueryable<T> filtering happens on the database server; only matching rows cross the network.
Converting an IQueryable<T> to IEnumerable<T> too early (via ToList()/AsEnumerable()) pulls the full table into memory before filtering.
Methods the provider can't translate into SQL throw a runtime exception when used inside an IQueryable expression.
Best practice: apply Where, OrderBy, and Take while still IQueryable<T>, and materialize with ToList() last.