IEnumerable vs IQueryable in EF Core: Why the Wrong Type Loads Your Whole Table
Queryable.Where takes an Expression<Func<T, bool>>, a data structure EF Core can read and translate into SQL. Enumerable.Where takes a plain Func<T, bool>, a compiled delegate EF Core cannot see inside. So the moment a query is typed as IEnumerable<T>, every operator after it runs in your process - and EF Core fetches every row of the table, and tracks them, to feed it.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.
IEnumerable<Order> instead of IQueryable<Order> silently binds every subsequent Where, OrderBy and Take to the in-memory version.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 loadedThe 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.
db.Orders is a tracking query by default, and tracking happens at materialisation - not at filtering. So every row you fetch, including every one your delegate is about to throw away, gets a snapshot in the change tracker. The DbContext ends up holding the whole table, and a later SaveChanges has to walk all of it. If you must pull rows to the client, AsNoTracking() at least stops EF Core from paying for entities you are only going to discard.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.
IQueryable. It fires when EF Core tries to translate your predicate and fails. Type the variable as IEnumerable and the predicate never reaches EF Core at all - the compiler bound it to Enumerable.Where, so there is nothing to translate, nothing to fail, and nothing to throw. You do not get an exception. You get a SELECT with no WHERE clause, and no warning that it happened.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.WhereLook 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>.
AsEnumerable, ToList, AsQueryable - which and when?
Switching to the client is sometimes exactly what you want. The docs list two good reasons: the amount of data is small, or the LINQ operator has no server-side translation. The point is to do it deliberately, and after the database has already done the heavy lifting.
// Narrow on the server FIRST, then hand off to C#
var flagged = (await db.Orders
.Where(o => o.PlacedAt > cutoff) // SQL
.Where(o => o.Total > 1_000) // SQL
.ToListAsync(ct)) // executes here - a small set
.Where(o => IsSuspicious(o)) // C# method, impossible in SQL
.ToList();| Call | What it does |
|---|---|
AsEnumerable() | Switches to client evaluation but stays deferred - it streams the results. Use it when you will enumerate once. |
ToList() | Executes immediately and buffers into a list, which "takes additional memory". Worth it if you enumerate more than once, since it is a single trip to the database. |
AsQueryable() | On an in-memory collection this gives you nothing - no SQL, no translation. It wraps the list in a queryable façade that still executes in memory. It is useful for satisfying a signature, not for performance. |
List<T> in AsQueryable() hands you an EnumerableQuery<T>, whose provider is not an IAsyncQueryProvider. So the moment the code under test calls an async EF Core operator on it - ToListAsync, FirstOrDefaultAsync, CountAsync - it throws at runtime, because the source is not an IAsyncEnumerable. That is why libraries like MockQueryable exist at all, and why the EF Core testing docs point away from this entirely: they call mocking DbSet for querying "complex and difficult" and discourage it, steering you toward SQLite in-memory mode or your real database system instead.One nuance that is easy to get backwards: client evaluation in the final projection is allowed. EF Core "supports partial client evaluation in the top-level projection (essentially, the last call to Select())". So calling a C# helper inside your last Select works without any AsEnumerable - EF fetches the columns it needs and runs your method on the results. It is only in a Where, an OrderBy, or a join that it throws.
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. 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?
| Situation | Use |
|---|---|
| Building a query the database should execute | IQueryable<T>, all the way to the call that materialises it. |
| A repository method callers need to compose on | Return IQueryable<T> - or, better, do not let them compose: take the filter as a parameter and return a projected DTO. Returning IEnumerable from a repository is the classic way this goes wrong. |
| C# logic inside a filter or ordering | Narrow on the server first, then AsEnumerable() or ToListAsync(), then apply it. |
C# logic inside the final Select | Nothing to do - EF Core allows client evaluation in the top-level projection. |
| A collection already in memory | IEnumerable<T>. AsQueryable() will not conjure a database. |
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
The type of the lambda the LINQ operator accepts. Queryable.Where takes an Expression<Func<T, bool>> - an expression tree EF Core can inspect and translate into SQL. Enumerable.Where takes a Func<T, bool> - a compiled delegate that can only be invoked, never inspected. IQueryable also inherits from IEnumerable, which is why the mistake compiles silently.
Because the compiler binds every subsequent operator to the in-memory versions. EF Core is never given the predicate, so it cannot put it in the WHERE clause. It sends a SELECT with no WHERE, materialises every row into an entity, and your delegate filters them in your process. Worse, a tracking query snapshots every one of those entities in the change tracker - including the ones you are about to discard. The results are still correct, which is why it passes tests and review; the only place the difference shows up is the SQL.
Yes, but only inside IQueryable. Since EF Core 3.0, an expression it cannot translate anywhere other than the top-level projection causes a runtime exception rather than a silent client-side fallback. That guard fires when EF tries to translate and fails. If you typed the variable as IEnumerable, EF is never asked to translate anything, so there is nothing to fail and nothing to throw - you just get a table scan.
Both switch to client evaluation. AsEnumerable stays deferred and streams the results; ToList executes immediately and buffers everything into a list, which costs additional memory. Prefer AsEnumerable when you enumerate once, and ToList when you enumerate several times, since the list means only one trip to the database.
No. AsQueryable on an in-memory collection wraps it in a queryable façade whose provider still executes everything in memory. There is no database behind it and no SQL is generated. It is useful for satisfying a method signature, never for performance. And be careful using it to fake a DbSet in tests: an EnumerableQuery has no async query provider, so any async EF Core operator - ToListAsync, FirstOrDefaultAsync, CountAsync - throws at runtime, which is why the EF Core testing docs discourage mocking DbSet and point to SQLite in-memory or your real database instead.
It is a genuine trade-off. Returning IQueryable keeps queries composable and translatable, but it leaks EF Core into the caller and lets anyone write a query you never reviewed. Returning IEnumerable hides EF Core but silently kills translation. A middle path is to accept the filter as a parameter and return a projected DTO, so composition happens inside the repository where you control it.