Introduction
When developers first write EF Core code, the immediate goal is just to "Make it work." The application runs smoothly on a local machine with small amounts of data, the API is fast, and there are no errors. This creates a false sense of security.
However, once that same code is deployed to a production environment with thousands of records, performance problems suddenly appear. APIs become slow, memory usage increases, and the database load spikes, leading to user complaints. The confusing part is that there isn't a technical error in the code itself.
Small datasets hide these inefficiencies; a query that's fine with 20 records becomes expensive with thousands. This article series addresses common, seemingly innocent EF Core patterns that cause these real-world production issues. The explanations are simple, practical, and require no deep SQL knowledge, helping developers build safer, more efficient habits right away.
Lets explore this in this 3 part series. In this part 1 lets focus on Query Execution Mistakes.
When we write EF Core queries, everything often looks fine on a local database with limited data.But once the same code hits production, performance suddenly drops.In this article, we’ll look at three common query execution mistakes that silently hurt performance—and how to fix them the right way.
Query Execution Mistakes
1. Using Count() After ToList() – The Double Query Trap
When you need to retrieve both data and a total record count, it is easy to write code that seems logical but performs poorly. A common mistake is calling ToListAsync() to pull all records into your application's memory first, and then using the .Count property on that list. While this works on a small scale, you are essentially forcing your application to do heavy lifting that the database is already optimized for. You didn't actually need every single row of data just to get a simple number; you only needed the count itself.
The innocent code:
var orders = await _context.Orders.ToListAsync();
var count = orders.Count;
In reality, this approach creates a "Double Query Trap" where the database does far more work than required. By loading the entire table into memory, you consume excessive RAM and slow down your API. The correct fix is to use CountAsync() directly on the database query. This ensures that the database only sends back a single integer—the count—rather than thousands of rows of data.
The correct fix:
var count = await _context.Orders.CountAsync();
The "reality check" here is simple: if you only wanted a number, don't force your application to download the entire table just to find it
2. Sorting After Fetching – Database vs Memory Fight
It is common for sorting to work perfectly fine during local development, only to cause significant slowdowns once the application hits production. This usually happens when you fetch an entire list of data from the database before applying a sort order. By callingToListAsync() first, you are forcing the database to return raw, unsorted data, which your application then has to organize in its own memory. This process is inefficient because it completely ignores database indexes that are specifically designed for high-speed sorting.
The code:
var users = await _context.Users.ToListAsync();
var sorted = users.OrderBy(u => u.Name);
The primary issue here is that sorting happens in the application memory rather than at the database level. While your laptop might handle a few dozen records easily, a production server struggling with thousands of rows will quickly become bogged down. Databases are built to sort massive amounts of information in milliseconds using optimized structures. When you sort in code, you lose that speed and waste your server's CPU and RAM.
Fix:
var users = await _context.Users
.OrderBy(u => u.Name)
.ToListAsync();
The lesson to remember is that the database is almost always faster at organizing data than your application is. By moving the OrderBy call before the ToListAsync, you allow the database to do the heavy lifting. As the saying goes: "The database could sort in milliseconds; you forced your app to do it instead."
3. Filtering After ToList() – The Fake Optimization
A common mistake occurs when developers decide to "fetch everything now and filter in the code later." While this might seem flexible during development, production environments will quickly push back against this approach. By calling
ToListAsync() before applying a filter, you are instructing the database to package up every single row in the table and send it over the network to your application. This bypasses the database’s most powerful feature: the ability to quickly find specific records using indexes.
The code:
var orders = await _context.Orders.ToListAsync();
var recent = orders.Where(o => o.Date >= DateTime.Today);
When this code runs, the entire table is loaded into your application's memory, and the filtering happens in the C# code instead of the SQL engine. This is essentially the opposite of optimization; you are wasting network bandwidth, consuming massive amounts of RAM, and forcing your app to do work that the database could have done much faster. In a production setting with millions of rows, this can lead to "Out of Memory" errors or extremely long wait times for users.
The fix:
var recent = await _context.Orders
.Where(o => o.Date >= DateTime.Today)
.ToListAsync();
The correct approach is to place the Where clause before the execution command. This ensures that the filtering happens at the source, so only the relevant records ever leave the database. As the "truth bomb" of performance tuning states: "Filtering after fetching is not optimization. It’s wasted work."
Conclusion
In this article, we have seen how common performance bottlenecks in EF Core often stem from the single root cause of executing queries too early, typically through premature use of ToListAsync(). Once this method is called, the database finishes its work and loads data into memory, eliminating the ability to utilize database indexes for efficient filtering or sorting. To optimize performance, it is crucial to keep logic within the IQueryable stage to ensure the database handles data operations efficiently.