EF Core Query Optimization: From 30 Seconds to 30 Milliseconds

A kirana shop story

Imagine you run a small kirana shop. A customer hands you a list of 50 items.

You could walk to the back room, grab one item, walk back, then walk to the back room again for the next item, and again, and again. Fifty trips for fifty items. By the end you are tired and the customer has left.

A smarter shopkeeper reads the whole list first, walks to the back room once, fills a basket with all 50 items, and walks back. One trip.

EF Core is the same. A slow query usually makes too many trips to the database, or carries back far more than it needs. Query optimization means making fewer trips and carrying only what you need. In this article we will take one painfully slow query and bring it down step by step, from about 30 seconds to about 30 milliseconds.

Our slow starting point

Here is a query that looks innocent. It lists orders for a dashboard, showing the customer name and how many items each order has.

var orders = await context.Orders.ToListAsync();
 
var report = new List<OrderRow>();
foreach (var order in orders)
{
    // each line here secretly hits the database again
    var customer = order.Customer;
    var lineCount = order.Lines.Count;
 
    report.Add(new OrderRow
    {
        OrderId = order.Id,
        CustomerName = customer.Name,
        ItemCount = lineCount
    });
}

It works. But on a table with 10,000 orders it takes around 30 seconds. Let us find out why and fix it one layer at a time.

Step 1: kill the N+1 problem

The biggest villain here is the N+1 problem. Look again at the loop. The first line, context.Orders.ToListAsync(), runs one query. But inside the loop, every time we touch order.Customer or order.Lines, EF Core sneaks off and runs another query to fetch that data.

So for 10,000 orders we run 1 query for the list, plus 2 queries per order. That is roughly 20,001 queries. Each one is a round trip to the database. This is exactly the tired shopkeeper making one trip per item.

The fix is eager loading. We tell EF Core up front which related data we need, using Include. Now EF Core fetches everything in a small number of queries instead of thousands.

var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines)
    .ToListAsync();

This single change takes the query from about 30 seconds to roughly 4 seconds. We went from 20,001 trips down to a handful. Huge win, and we have barely started.

Step 2: do not carry the whole shop home

We are faster, but we are still carrying too much. Include(o => o.Lines) pulls every column of every order line into memory, just so we can count them. That is like carrying home every single grain of rice to find out how many bags you bought.

The cure is a projection. Use Select to ask only for the exact fields you need. EF Core then writes SQL that reads just those columns, and it can count the lines in the database instead of in your app.

var report = await context.Orders
    .Select(o => new OrderRow
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Lines.Count   // counted in SQL, not in memory
    })
    .ToListAsync();

Notice we no longer need Include at all. When you project with Select, EF Core figures out the joins for you. The database returns a tiny, flat result: three small values per row. No full entities, no extra columns. This drops the query to about 800 milliseconds.

Here is the difference in what travels across the network.

Step 3: stop tracking what you will not change

By default, EF Core tracks every entity it loads. Tracking means it keeps a private copy of each object so that later, if you change something and call SaveChanges, it knows what to update. That is useful when you are editing data. It is wasted effort when you are only displaying it.

Our dashboard is read-only. We never save anything. So we can switch tracking off with AsNoTracking. EF Core then skips building all those snapshots, which saves both time and memory.

var report = await context.Orders
    .AsNoTracking()
    .Select(o => new OrderRow
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Lines.Count
    })
    .ToListAsync();

One small note: when you project into a custom class like OrderRow (not an entity), EF Core does not track those results anyway, so AsNoTracking matters most when you return real entities. It is still a good habit to add it on every read-only query so your intent is clear. With full entity reads this step alone can cut time and memory by a noticeable chunk. For our projected query it shaves us down to around 400 milliseconds.

Step 4: give the database a good index

So far we improved the C# side. Now we look at the database itself. Suppose our dashboard usually filters orders by date, like "show me last week's orders":

var report = await context.Orders
    .AsNoTracking()
    .Where(o => o.CreatedOn >= lastWeek)
    .Select(o => new OrderRow
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Lines.Count
    })
    .ToListAsync();

If the CreatedOn column has no index, the database must read every single row and check its date. That is a full table scan. On a big table this is slow no matter how clean your LINQ is.

An index is like the alphabetical thumb tabs on a dictionary. Instead of flipping every page, you jump straight to the right section. We add an index in our model configuration:

modelBuilder.Entity<Order>()
    .HasIndex(o => o.CreatedOn);

After running the migration, the database can jump straight to the matching dates. Our filtered query now finishes in about 60 milliseconds.

A quick guide on what to index:

Do not index every column. Each index makes writes a little slower and takes disk space, so add them where your real queries need them.

Step 5: compiled queries for the hottest paths

We are already at around 60 milliseconds, which is great. But what if this exact query runs thousands of times a second on a busy server? There is one more trick: compiled queries.

Every time you run a LINQ query, EF Core has to translate that expression tree into SQL. EF Core caches this translation, so it is fast after the first call. But a compiled query goes one step further and hands you a ready-made delegate, skipping even the cache lookup. For a tiny query that runs constantly, this removes a little overhead each time.

private static readonly Func<AppDbContext, DateTime, IAsyncEnumerable<OrderRow>>
    GetRecentOrders = EF.CompileAsyncQuery(
        (AppDbContext context, DateTime since) =>
            context.Orders
                .AsNoTracking()
                .Where(o => o.CreatedOn >= since)
                .Select(o => new OrderRow
                {
                    OrderId = o.Id,
                    CustomerName = o.Customer.Name,
                    ItemCount = o.Lines.Count
                }));

You call it like a normal method:

var report = new List<OrderRow>();
await foreach (var row in GetRecentOrders(context, lastWeek))
{
    report.Add(row);
}

This is an advanced tool. For most screens the normal cached query is already fast enough, and compiled queries add code that is harder to read. Use them only on proven hot paths, after measuring. With this final touch our query settles at around 30 milliseconds.

The full journey

Here is everything we did and what each step bought us. Your real numbers will differ, but the order of impact is what matters: fix N+1 first, it is almost always the biggest win.

How to see what EF Core is really doing

You cannot fix what you cannot see. Before optimizing, turn on logging so you can watch the actual SQL and count the queries. The simplest way during development is to log to the console:

optionsBuilder
    .UseSqlServer(connectionString)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging(); // dev only, never in production

Now run your screen and look at the output. If you see the same query repeated many times with different ids, you have an N+1 problem. If you see one query pulling dozens of columns when you only show two, you need a projection. The logs tell you exactly which step above to apply.

A word of caution on EnableSensitiveDataLogging: it prints real parameter values, including things like emails or names. Keep it only in development, never in production.

Common mistakes to avoid

A few traps catch people again and again:

• Calling ToList too early. If you write context.Orders.ToList().Where(...), you pull the whole table into memory and filter in C#. Keep the Where before ToList so the database does the filtering.
• Using lazy loading without realizing it. Lazy loading quietly causes N+1. Prefer explicit Include or projections so every database trip is visible in your code.
• Counting in memory. order.Lines.Count on a loaded list is fine, but orders.Sum(o => o.Lines.Count) after loading everything is wasteful. Let SQL count when you can.
• Indexing everything. More indexes slow down inserts and updates. Add them where queries need them, not everywhere.
• Optimizing without measuring. Always log the SQL first. Guessing wastes time and sometimes makes things worse.

Quick recap

• A slow EF Core query usually makes too many database trips or carries too much data. Fix both.
• The N+1 problem is the number one cause. Use Include (eager loading) or projections so you fetch related data in one planned go, not one query per row.
• Use a projection with Select to read only the columns you need. This is often the single biggest easy win for list and report screens.
• Add AsNoTracking to read-only queries so EF Core skips building change-tracking snapshots, saving time and memory.
• Add a database index on columns you filter, sort, or join on, so the database can seek instead of scanning every row.
• Reach for compiled queries only on proven hot paths, after measuring, because they add complexity.
• Always turn on SQL logging first. Measure, fix the biggest problem, measure again.

References and further reading

• Efficient Querying — EF Core (Microsoft Learn)
• Advanced Performance Topics — EF Core (Microsoft Learn)
• Single vs. Split Queries — EF Core (Microsoft Learn)
• 5 EF Core Query Techniques for .NET 10 (TheCodeMan)
• How To Increase EF Core Performance for Read Queries (Anton DevTips)