IQueryable vs IEnumerable: The Mistake That Loads Your Whole Table

#efcore #dotnet #csharp #linq

You wrote a clean LINQ query. It filters down to ten rows. It runs fine in dev, and then in production it's slow and your database CPU is higher than it should be for such a small result.
The query looks right. The problem is where the filtering happens. With IEnumerable, the filter runs in your app after the whole table is already loaded from the database. With IQueryable, the filter becomes part of the SQL and runs in the database. Same LINQ, completely different amount of data moving across the wire.
Get this wrong on a large table and you're pulling every row into memory to throw almost all of them away.

The Two Interfaces
Both let you write .Where(), .Select(), .OrderBy(). That's why they get mixed up. The difference is when and where the query executes.
IEnumerable works on objects already in memory. When you call .Where() on it, the filtering happens in your application, in C#, row by row. If the data came from a database, it's already been loaded in full before your filter ever runs.
IQueryable builds an expression tree instead of running anything immediately. EF Core takes that tree and translates it into SQL. Your .Where() becomes a SQL WHERE clause, and the database does the filtering before it sends anything back.
Same method names. One filters in the database, the other filters in your app after the table is already loaded.

Where It Goes Wrong
Here's the line that causes it:

public IEnumerable<Order> GetOrders()
{
    return _db.Orders;   // returns IEnumerable
}

The method returns IEnumerable. The moment you expose it as IEnumerable, any .Where() a caller adds runs in memory, not in SQL:

`var recent = GetOrders().Where(o => o.CreatedAt > lastWeek);`

That .Where() looks like it filters in the database. It doesn't. GetOrders() already returned an IEnumerable, so EF Core loads the entire Orders table into memory, and then C# filters it down to last week's rows. On a table with a few hundred rows you'd never notice. On a table with two million, it's a disaster.
Now the version that does what you expect:

public IQueryable<Order> GetOrders()
{
    return _db.Orders;   // returns IQueryable
}

var recent = GetOrders().Where(o => o.CreatedAt > lastWeek).ToList();

Because GetOrders() returns IQueryable, the .Where() gets folded into the query, and EF Core generates SQL like this:

SELECT [o].[Id], [o].[CustomerId], [o].[CreatedAt], [o].[Total]
FROM [Orders] AS [o]
WHERE [o].[CreatedAt] > @lastWeek

The database filters first and sends back only the rows you asked for. Same calling code. One returns two million rows over the wire, the other returns a handful.

The Trap Is the Return Type
The mistake almost always hides in a method signature. Someone writes a repository or service method that returns IEnumerable because it feels safer or more general. Every caller that adds a filter after that point is filtering in memory, and nobody notices until the table grows.
AsEnumerable() and ToList() do the same thing on purpose. The moment you call either, everything after it runs in memory:

var recent = _db.Orders
.ToList()                              // whole table loaded here
    .Where(o => o.CreatedAt > lastWeek);   // filters in memory

Move the .Where() before the .ToList() and it runs in SQL. The order matters, and it's easy to get backwards.

When You Actually Want IEnumerable
This isn't "always use IQueryable." Once the data is in memory, IEnumerable is the right type, and forcing IQueryable where it doesn't belong just adds confusion.
If you've already loaded a list and you're filtering it in code, that's IEnumerable and that's correct. If you're working with an in-memory collection that never touched a database, there's no SQL to translate to, so IQueryable buys you nothing. And there are queries EF Core can't translate to SQL — certain method calls or custom C# logic — where you have to pull the data into memory first and finish the work there. That's a real case, just do it deliberately, not by accident through a return type.
The point isn't to fear IEnumerable. It's to know which one you're holding and where the filtering will run.

Our Take
At Qodors, when a .NET app has queries that are slow out of proportion to the data they return, this is one of the first things we look at. The query reads fine. The LINQ is correct. The problem is a method three layers down that returns IEnumerable, quietly loading a whole table so the app can filter it in memory.
It hides well because it doesn't fail. Small tables in development behave normally, tests pass, and it only turns into a problem when real data volume shows up. By then it looks like a database performance issue, and people go tuning indexes when the fix is a return type change from IEnumerable to IQueryable.
Check what your repository and service methods return. If they hand back IEnumerable from an EF Core query, your filtering probably isn't happening where you think it is.
Quick Checklist
Return IQueryable from repository/service methods that wrap EF Core queries, so callers filter in SQL
Watch every ToList() and AsEnumerable() — everything after it runs in memory
Keep .Where() and .Select() before you materialize with ToList()
Use IEnumerable for data that's already in memory — that's what it's for
If a query genuinely can't translate to SQL, pull it into memory on purpose, not by accident
When a query is slow for the rows it returns, check the return types up the call chain
IQueryable and IEnumerable share the same LINQ methods, which is exactly why the bug is easy to write and hard to spot. One filters in the database. The other loads the table first and filters in your app.
Know which one your method returns. That one decision is the difference between a query that touches ten rows and one that drags two million across the wire.