EF Core query performance that actually feels fast

You know that feeling when an API that was snappy yesterday suddenly needs a coffee break today? I once watched a dashboard endpoint turn a sub 200 ms request into a 6 second slog. The database server sounded like a TIE fighter and my CPU fan tried to take flight. The fix did not require dark magic. It required fetching less.

In this post we will walk through practical EF Core habits that make queries fast. We will project only what we need, avoid accidental N+1s, stop calling ToList too early, opt into AsNoTracking for reads, choose AsSplitQuery when includes explode, paginate, use indexes, and even compile hot queries. All with small, runnable C# snippets.

The real bottleneck is not EF Core

Databases are great at set based work but they live across a network and they charge rent in I/O. The larger the result and the more joins, the more you pay in latency, CPU and memory. The cheapest byte is the one you never asked for. So the first rule is simple. Shape your data at the query.

1) Projection beats Include when you do not need the whole graph

If your UI only needs a few fields, do not pull full entities. Project to a DTO and let the database do the shaping.

var dashboard = await db.Orders
    .Where(o => o.Status == OrderStatus.Active)
    .Select(o => new OrderDto(
        o.Id,
        o.Customer.Name,
        o.Items.Sum(i => i.Price)))
    .AsNoTracking()
    .ToListAsync();

Why this is faster

• You eliminate wide columns you do not need
• You avoid change tracking cost
• You reduce network payload

2) AsNoTracking for read only scenarios

Tracking is awesome when you plan to modify entities. For reads, it adds overhead you do not need.

var recent = await db.Customers
    .AsNoTracking()
    .OrderByDescending(c => c.CreatedAt)
    .Take(20)
    .Select(c => new { c.Id, c.Name, c.Email })
    .ToListAsync();

Rule of thumb

• If you will not call SaveChanges on the materialized entities, prefer AsNoTracking

3) Filter early and do not call ToList until the end

Materializing too soon moves work from SQL to LINQ to Objects. That means more memory and slower filtering.

// Slower: filters in memory
var slow = (await db.Orders.ToListAsync())
    .Where(o => o.Status == OrderStatus.Active)
    .ToList();

// Faster: filters in SQL
var fast = await db.Orders
    .Where(o => o.Status == OrderStatus.Active)
    .ToListAsync();

4) Avoid the N+1 trap

Accessing navigation properties inside a loop can trigger one query per row. That is like asking a barista to make 100 separate espressos instead of one carafe.

// N+1: one for orders, then one per customer
foreach (var o in await db.Orders.ToListAsync())
{
    Console.WriteLine(o.Customer.Name);
}

// Single round trip with projection
var names = await db.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToListAsync();

5) AsSplitQuery when includes get heavy

Multiple Includes can create a huge join that duplicates rows. Splitting the query reduces the cartesian blast radius while keeping a single logical load.

var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync();

Tip

• Use AsSplitQuery when you have multiple collections included and you see row explosion

6) Always paginate

Even the best query crawls when you ask for thousands of rows at once. Paginate and let the client ask for more when it needs to.

app.MapGet("/orders", async (AppDbContext db, int page) =>
{
    const int size = 50;
    var data = await db.Orders.AsNoTracking()
        .OrderByDescending(o => o.CreatedAt)
        .Skip((page - 1) * size).Take(size)
        .Select(o => new OrderDto(o.Id, o.Customer.Name, o.Items.Sum(i => i.Price)))
        .ToListAsync();
    return Results.Ok(data);
});

7) Index for the way you actually query

EF Core translates LINQ into SQL but the database decides how fast it can seek. Create indexes for common filters and joins.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.Status });
}

Notes

• Index foreign keys by default
• Composite indexes should match your Where and Join patterns

8) Compiled queries for hot paths

If the same query runs thousands of times per minute, skip repeated translation and caching overhead with a compiled query.

static readonly Func<AppDbContext, int, OrderDto?> GetOrderById =
    EF.CompileQuery((AppDbContext ctx, int id) =>
        ctx.Orders.AsNoTracking()
            .Where(o => o.Id == id)
            .Select(o => new OrderDto(o.Id, o.Customer.Name, o.Items.Sum(i => i.Price)))
            .FirstOrDefault());

Usage

var dto = GetOrderById(db, 42);

9) When is Include the right choice

Projection should be your default, but Include shines when you plan to modify the loaded graph or when you truly need the entities as entities. If you only need counts or a few fields, prefer selective projection.

// Good use of Include for updates
var order = await db.Orders
    .Include(o => o.Items)
    .FirstAsync(o => o.Id == id);
order.Items.RemoveAll(i => i.IsDiscontinued);
await db.SaveChangesAsync();

A tiny end to end story

Meet Dwight, a product manager who wants a lightning fast dashboard. Dwight only needs order id, customer name and total. He does not need the entire object graph to make a pie chart. We project exactly that shape, add AsNoTracking, paginate, and ship it. Load goes up, latency stays low, and somewhere a server fan breathes a sigh of relief.

Wrap up

Fast EF Core is mostly about restraint. Shape results with projection, keep tracking off when you do not need it, filter early, sidestep N+1, split big includes, page every list and give the database the indexes it deserves. Your APIs will feel lighter, your database will work smarter and your users will stop timing their requests with a calendar.