Shaving 200ms Off Every DB Query with EF Core

The Wake-Up Call

Our dashboard was taking 800ms to load. Users were complaining. When we profiled it, we found EF Core was firing 47 separate queries to render a single page — the classic N+1 problem — and three of those queries were doing full table scans on a 2-million-row table.

Six hours of focused work brought that to 180ms. Here's exactly what we did.

1. Kill N+1 With Eager Loading

The most common EF Core performance killer is lazy loading or forgotten Include() calls. If you load a list of orders and then access order.Customer in a loop, EF fires a new query per order.

// Bad — N+1 queries
var orders = await db.Orders.ToListAsync();
foreach (var o in orders) Console.WriteLine(o.Customer.Name); // query per row!

// Good — single JOIN
var orders = await db.Orders
    .Include(o => o.Customer)
    .ToListAsync();

If you only need a couple of fields from the related entity, use a projection instead — it avoids loading the full entity and lets EF write a tighter SQL query:

var orders = await db.Orders
    .Select(o => new { o.Id, o.Total, CustomerName = o.Customer.Name })
    .ToListAsync();

2. Add the Right Indexes

EF Core will create primary key indexes automatically, but it won't create indexes on foreign keys or query filter columns unless you tell it to. Use HasIndex() in OnModelCreating:

modelBuilder.Entity<Order>()
    .HasIndex(o => o.CustomerId);   // FK index
modelBuilder.Entity<PageView>()
    .HasIndex(p => p.UtcTime);      // sort/range column
modelBuilder.Entity<User>()
    .HasIndex(u => u.Email).IsUnique();

The single biggest win on our dashboard was adding an index on utc_time — the date-range filter column in our analytics table. Query time dropped from 340ms to 12ms.

3. Use AsNoTracking for Read-Only Queries

By default, EF Core tracks every entity it loads so it can detect changes for SaveChanges(). That tracking has overhead — both CPU and memory. If you're only reading data (list pages, reports, APIs), turn it off:

var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

On our product listing page (200 rows), AsNoTracking() alone cut memory allocations by 40%.

4. Compiled Queries for Hot Paths

For queries that run on every request (auth checks, permission lookups), EF Core's compiled queries eliminate the LINQ expression tree parsing overhead:

private static readonly Func<AppDbContext, string, Task<User?>> _getUserByEmail
    = EF.CompileAsyncQuery((AppDbContext db, string email) =>
        db.Users.FirstOrDefault(u => u.Email == email));

// Usage — no LINQ parsing on hot path
var user = await _getUserByEmail(db, email);

5. Pagination — Never Skip Large Offsets

Standard Skip()/Take() pagination gets slow as you move to higher page numbers because the database still has to scan and discard all the preceding rows. For large tables, use keyset pagination instead:

// Slow for page 500+
var page = await db.PageViews
    .OrderByDescending(p => p.UtcTime)
    .Skip(pageNum * pageSize).Take(pageSize)
    .ToListAsync();

// Fast at any page — cursor-based
var page = await db.PageViews
    .Where(p => p.UtcTime < lastSeenTime)
    .OrderByDescending(p => p.UtcTime)
    .Take(pageSize)
    .ToListAsync();

The Result

After these five changes, our dashboard load time went from 800ms to under 200ms with zero changes to business logic. EF Core is genuinely fast when you work with it, not against it.