Query Optimization in EF Core

Real-world analogy

What is it?

EF Core query optimization means controlling what SQL your LINQ turns into. A query stays on the database side while you chain Where, OrderBy, Skip, Take and Select on the IQueryable. It moves into memory the moment you call ToList, ToArray or iterate it. The goal is to send few queries, return few columns and rows, and let the database do the filtering, sorting and counting, because it has the indexes.

Real-world relevance

A list page that felt fast with ten test rows takes eight seconds with fifty thousand. The usual causes are a loop that triggers one query per row, a screen that loads whole entities to show two fields, and paging done in memory. Developers find them by reading the logged SQL, not by guessing. Fixing them often turns hundreds of queries into one, with no change to what the user sees.

Key points

• The N+1 problem — One query loads a list, then one more query runs for each row to fetch related data. 100 rows mean 101 round trips.
• See the SQL EF Core sends — Turn on LogTo(Console.WriteLine) in development, or use a profiler. Repeated near-identical SELECT statements in the log are the N+1 signature.
• Project with Select — Select only the columns the screen needs into a DTO. The database sends less data, and nothing needs tracking.
• Include can multiply rows — Including two collections returns a row for each combination of their items, a cartesian explosion. AsSplitQuery() runs one query per collection instead.
• Filter and page in the database — Put Where, Skip and Take before ToListAsync. Add an OrderBy on a unique column, or pages can overlap.
• Keyset pagination scales better — Instead of Skip(100000), ask for rows where Id is below the last id you showed. The database jumps straight there through an index.
• Any beats Count greater than 0 — Any() becomes an EXISTS check that stops at the first match. Count() > 0 counts all the matching rows first.
• Client evaluation traps — Since EF Core 3.0, a Where it cannot translate throws an error. Calling ToList() first makes it work, but filters the whole table in memory.
• Compiled queries and raw SQL — Reach for EF.CompileAsyncQuery or FromSql only after measuring. Most slow queries need a better shape or an index, not a fancier API.

Code example

public record OrderRow(int Id, string CustomerName, decimal Total);

// Slow shape (lazy loading on): one query for orders, then one per order for its customer
//   var orders = await db.Orders.ToListAsync(ct);
//   foreach (var o in orders) names.Add(o.Customer.Name);

public async Task<List<OrderRow>> GetPaidPageAsync(ShopContext db, int page, int pageSize, CancellationToken ct)
{
    return await db.Orders
        .AsNoTracking()
        .Where(o => o.Status == "Paid")
        .OrderByDescending(o => o.Id)          // unique column keeps pages stable
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new OrderRow(
            o.Id,
            o.Customer.Name,                   // becomes a JOIN, not a query per row
            o.Items.Sum(i => i.Price * i.Quantity)))
        .ToListAsync(ct);
}

public Task<bool> HasPaidOrdersAsync(ShopContext db, int customerId, CancellationToken ct)
{
    // EXISTS in SQL: stops at the first match
    return db.Orders.AnyAsync(o => o.CustomerId == customerId && o.Status == "Paid", ct);
}

Line-by-line walkthrough

1. 1. OrderRow is a small record with only the three values the page shows.
2. 2. The commented lines show the slow shape: with lazy loading on, touching o.Customer in the loop runs one query per row.
3. 3. Where, OrderByDescending, Skip and Take all run before ToListAsync, so the database filters and pages.
4. 4. OrderByDescending on Id gives a stable order. Without one, rows can repeat or vanish between pages.
5. 5. The Select reads the customer name and a sum of line totals, and EF Core joins them in one SQL statement.
6. 6. HasPaidOrdersAsync uses AnyAsync, which becomes EXISTS and stops at the first match.

Spot the bug

public async Task<int> CountPaidAsync(ShopContext db)
{
    var all = await db.Orders.ToListAsync();   // loads every order

    var paid = all.Where(o => o.Status == "Paid").ToList();
    return paid.Count;
}

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Efficient querying in EF Core (Microsoft Learn)
• Single vs. split queries (Microsoft Learn)
• Pagination in EF Core (Microsoft Learn)