IEnumerable vs IQueryable in EF Core: Why the Wrong Type Loads Your Whole Table

Type a query as IEnumerable instead of IQueryable and it still returns exactly the same rows. That is what makes it hard to catch: it passes the tests, it passes review, and on a development database with a few thousand rows there is nothing to see. The only thing that differs is the SQL - and you can check that for yourself with ToQueryString(). One version filters in the database; the other fetches every row and filters in your process. What that costs is not a matter of opinion. It scales with the number of rows in the table.

What makes it worth an article is the second half of the story: EF Core has a safety net for exactly this problem, and typing IEnumerable is how you step outside it.

What is the actual difference?

Start with the fact that IQueryable<T> inherits from IEnumerable<T>. It is not an alternative to it - it is an extension of it, and what it adds is an Expression and a Provider.

That inheritance is why both of these compile, and why they look identical:

// Queryable.Where(this IQueryable<T>, Expression<Func<T, bool>>)
IQueryable<Order> q = db.Orders;
q = q.Where(o => o.Total > 100);

// Enumerable.Where(this IEnumerable<T>, Func<T, bool>)
IEnumerable<Order> e = db.Orders;
e = e.Where(o => o.Total > 100);

Same lambda, same result set, wildly different behaviour. In the first case the compiler builds an expression tree - a data structure describing "a comparison between the Total property and the constant 100". EF Core walks that tree and emits WHERE [o].[Total] > 100.

In the second case the compiler builds a compiled delegate. A delegate can only be invoked; it cannot be inspected. EF Core has nothing to read, so it does not even try. It sends a SELECT with no WHERE clause at all, materialises every row into an Order, and then your delegate filters them one by one in your process.

Read "subsequent" literally, because the position of the change is the whole story. Overload resolution happens at each call site, against the type the receiver has at that point. So IEnumerable<Order> e = db.Orders.Where(o => o.Total > 100); is fine - the Where ran on an IQueryable and was translated, and the assignment afterwards is only an upcast of a query that is already built. It does not rewrite history. What breaks is the other order: upcast first, then filter. That is why the damage happens at boundaries - a method that returns IEnumerable forces every caller into the second shape.

A piece of trivia that follows from this: the object behind an IEnumerable-typed variable is still the real IQueryable - only the static type changed. Enumerable.AsQueryable() notices that and hands the original query back rather than wrapping it, so calling .AsQueryable() on such a variable genuinely restores SQL translation. Worth knowing; not worth relying on. Fix the type instead.

Why does EF Core need an expression tree?

Because a LINQ query is not executed when you write it. As the docs put it: "When you call LINQ operators, you're simply building up an in-memory representation of the query. The query is only sent to the database when the results are consumed."

That representation is the expression tree. EF Core hands it to the database provider, which "identifies which parts of the query can be evaluated in the database" and translates those parts to SQL. The query goes to the server only when you consume it - iterating in a foreach, or calling ToList, ToArray, Single, Count, or their async overloads.

So the tree is the contract. Remove it and EF Core is blind.

What goes wrong in real code?

One place it surfaces is a repository or service boundary, where the "safer looking" interface gets returned:

public class OrderRepository
{
    // Looks harmless. This is where it goes wrong.
    public IEnumerable<Order> GetOrders() => _db.Orders;
}

// Caller, three files away, with no idea
var big = repo.GetOrders()
    .Where(o => o.Total > 10_000)   // Func<Order, bool> - in memory
    .OrderByDescending(o => o.Total) // in memory
    .Take(20)                        // in memory
    .ToList();                       // the whole table was already loaded

The caller wrote a reasonable-looking query, and it returns the right twenty orders. But the database received a SELECT over the whole table with no WHERE, no ORDER BY and no TOP - which you can confirm from the SQL log, not from the C#. The cost of that is simple arithmetic: you pay for every row in the table, every time. On ten thousand rows that is invisible. On ten million, you are transferring and materialising ten million.

Be precise about what that costs, because it is worse than "it loads the table". Every row crosses the network and is materialised into an entity object. Whether they are all held in memory at once depends on the operators: a bare Where streams and discards as it goes, but OrderByDescending in LINQ-to-Objects has to see every element before it can sort even one - so in the example above, the entire table is buffered.

Why doesn't EF Core throw and save you?

This is the part that matters most, and it is easy to overlook.

EF Core does have a guard. Since version 3.0, if it finds something it cannot translate anywhere other than the final Select, it refuses to silently fall back to memory. The docs are explicit: "If EF Core detects an expression, in any place other than the top-level projection, which can't be translated to the server, then it throws a runtime exception." And on filters specifically: "Because the filter can't be applied in the database, all the data needs to be pulled into memory to apply the filter on the client... So Entity Framework Core blocks such client evaluation and throws a runtime exception."

That guard is why an untranslatable C# method inside a Where blows up loudly instead of quietly scanning your table. It is a genuinely good safety net.

That asymmetry is worth sitting with. An untranslatable query throws. A query you accidentally moved to the client succeeds. The louder failure is the safer one.

The overload trap that does this behind your back

You do not need to type IEnumerable on a variable to fall into this. A helper method with the wrong parameter type is enough. And the way the compiler reacts to it is the most instructive thing in this article.

// Source is IQueryable, predicate is a delegate.
// Overload resolution picks Enumerable.Where, which returns
// IEnumerable<T> - so this does NOT compile. CS0266.
public static IQueryable<T> Filter<T>(
    IQueryable<T> source,
    Func<T, bool> predicate)
    => source.Where(predicate);

// Same mistake, but the signature says IEnumerable.
// Now it compiles - and silently runs on the client.
public static IEnumerable<T> Filter<T>(
    IQueryable<T> source,
    Func<T, bool> predicate)
    => source.Where(predicate);

// Correct: keep it an expression all the way down.
public static IQueryable<T> Filter<T>(
    IQueryable<T> source,
    Expression<Func<T, bool>> predicate)
    => source.Where(predicate);        // binds to Queryable.Where

Look at what separates the first two. In both, the source really is an IQueryable, but the predicate is a delegate - so Queryable.Where is not even a candidate (a Func does not convert to an Expression), and overload resolution falls to Enumerable.Where, which hands back an IEnumerable<T>.

The first version therefore refuses to compile. IQueryable derives from IEnumerable, so the conversion goes that way and not the other - you cannot return an IEnumerable<T> where an IQueryable<T> was promised. The type system catches you.

The second version compiles perfectly, because the signature already gave up. That is the whole lesson in two lines: the type system will defend you for exactly as long as you keep asking for IQueryable. The moment a signature says IEnumerable, it stops arguing.

The rule: if a method takes a predicate that must reach the database, its type is Expression<Func<T, bool>>. Never Func<T, bool>.

How do you prove which side you are on?

Do not guess from the type - read the SQL. On any IQueryable, ToQueryString() (EF Core 5.0 and later) gives you the statement EF Core would send:

var query = db.Orders.Where(o => o.Total > 100);
Console.WriteLine(query.ToQueryString());
// SELECT [o].[Id], [o].[Total], ...
// FROM [Orders] AS [o]
// WHERE [o].[Total] > 100.0

If the WHERE you expect is missing, your filter is running in memory. (It is a debugging aid, so read it as indicative rather than exact - what it renders can differ marginally from what is actually executed, particularly in how parameter values are shown.) And note that you cannot even call ToQueryString() once the variable is an IEnumerable - the method does not exist there. That failure to compile is itself the answer.

When should you use each?

The habit that prevents all of it is small: keep IQueryable in the type until the moment you genuinely want rows in memory, and make that moment explicit. Every accidental IEnumerable is a decision to load the table, made by someone who did not know they were making it.

Inside a method body, var is the cheapest way to hold that line. It keeps whatever type the provider actually handed back, so you cannot declare your way onto Enumerable.Where - making the mistake requires writing IEnumerable<T> out by hand. What var cannot do is guard a boundary: parameters and return types have to be spelled out, and that is precisely where the overload trap did its damage. So: var inside methods, and deliberate IQueryable and Expression<Func<T, bool>> types on every signature.

One last thing, once the filter is running in SQL: the same discipline applies to columns. A translated Where still materialises whole entities unless you project, so use Select into a DTO and stop paying for columns you never read. That is the rest of the same story, and the Efficient querying guidance covers it properly.

Frequently asked questions

Official resources

• How queries work - EF Core, Microsoft Learn
• Client vs. server evaluation - EF Core, Microsoft Learn
• IQueryable<T> interface - .NET API, Microsoft Learn
• Efficient querying - EF Core, Microsoft Learn