EF Core Optimization Guide: A Step-By-Step Guide to Faster Queries

"EF Core is slow, don't use it."

I heard this from so many developers.

And almost every time, the cause is the same: EF Core makes it very easy to write a query that loads far more data than you need, and then you blame it for speed.

Integration tests don't catch this.

In development, your queries complete in a few milliseconds for a database with a few hundred rows.
Then production grows to 2 million rows and the same endpoint takes 30 seconds.

I've spent a lot of time optimizing EF Core in production systems, and I know a set of techniques that work.

Some of them you need to apply daily, others - when you need to make it faster, or based on your specific datasets.

Let's dive in.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/ef-core-optimization-guide-step-by-step

Step 0: Measure Before You Optimize

All examples in this article use a shipment tracking domain:

public class Shipment
{
    public Guid Id { get; set; }
    public string Number { get; set; }
    public string OrderId { get; set; }
    public string Carrier { get; set; }
    public string ReceiverEmail { get; set; }
    public ShipmentStatus Status { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }

    public Address Address { get; set; }

    public List<ShipmentItem> Items { get; set; }
}

public class ShipmentItem
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }

    public Guid ShipmentId { get; set; }
    public Shipment Shipment { get; set; }
}

Note: all queries were tested on PostgreSQL. The same ideas apply to SQL Server, MySQL, and Oracle, though the generated SQL and the exact limits differ.

Most performance work goes wrong before a single line of code changes.

A query feels slow, someone puts a cache in front of it, and the endpoint gets faster.

The slow query is still there. It just runs less often, and it will hurt again the moment the cache misses or the data changes more frequently than you expected.

So the first step is finding out what SQL your LINQ actually produces.

Turn on query logging in development:

builder.Services.AddDbContext<ShipmentsDbContext>(options =>
{
    options.UseNpgsql(connectionString);

    if (builder.Environment.IsDevelopment())
    {
        options.LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }
});

LogTo writes every generated SQL statement to the console.
EnableSensitiveDataLogging includes the actual parameter values, so you can copy the statement straight into your database client and run it.

You can also enable EF Core logging through your logging configuration - set the Microsoft.EntityFrameworkCore.Database.Command category to Information and let your logging framework (Serilog, NLog) write the SQL to a file or a sink of your choice.

Note: never enable EnableSensitiveDataLogging in production. It writes parameter values, including emails, addresses, and anything else your queries filter on.

Once you have the SQL, run it with an execution plan (EXPLAIN ANALYZE in PostgreSQL, or the actual execution plan in SQL Server).
The plan tells you whether the database is scanning an entire table or using an index, and where the time is actually spent.

Let's explore the query plan for this small query (from the screenshot above) that finds a Shipment by a number:

var shipment = await context.Shipments
    .Include(x => x.Items)
    .Where(s => s.Number == shipmentNumber)
    .FirstOrDefaultAsync(cancellationToken);

Three numbers are worth checking every time:

• Does the database use a full table scan or an index?
• How many rows the database returns. If a query returns 50,000 rows to build a page of 100, the fix is in your LINQ in EF Core query.
• How many round-trips your code makes. One request per loop iteration is a different problem from one slow request.

For anything you plan to tune repeatedly, write a benchmark with BenchmarkDotNet so you have a number to compare against.

Now that you can see what's actually happening, let's explore ways we can improve performance.

Fetch Less Data

Almost every slow EF Core query is slow for the same reason: it moves more data out of the database than the code needs.

The three optimizations in this step all attack that.

1. Add the Right Indexes

In EF Core (migrations), it automatically creates indexes for primary keys and foreign keys.
It doesn't create indexes for the columns you filter and sort on.

If your shipment list screen filters by status and orders by creation date, the database has to scan the table on every request:

Add the index in your entity configuration (the database migration will create it):

public class ShipmentConfiguration : IEntityTypeConfiguration<Shipment>
{
    public void Configure(EntityTypeBuilder<Shipment> builder)
    {
        builder.HasKey(x => x.Id);

        builder.HasIndex(x => x.Number)
            .IsUnique();

        builder.HasIndex(x => new { x.Status, x.CreatedAt });
    }
}

The composite index on (Status, CreatedAt) matches the shape of the query.

Column order matters here.
The database can use this index to filter by:

• Status alone
• Status and then sort by CreatedAt

But it can't use it to sort by CreatedAt alone.

Put the column you filter on for equality first, and the column you range-filter or sort on second.

When most of your queries only care about a small slice of the table, a filtered index is cheaper to maintain:

builder.HasIndex(x => x.CreatedAt)
    .HasFilter("\"Status\" NOT IN (4, 5)");

This indexes only the shipments that are still in flight and skips the delivered and canceled rows, which make up most of the table over time.

Remember that indexes aren't free.
Every index has to be updated on every insert, update, and delete that touches its columns.
Add indexes only for the query shapes you actually run.

Indexes help the database find your rows quickly. The next step is making sure you ask for fewer of them.

2. Filter Early, in the Database

This is the mistake I see most often from young developers (sometimes AI does this, duh!), and it usually hides behind a helper method:

// Loads every shipment in the table
var shipments = await dbContext.Shipments.ToListAsync(cancellationToken);

var pending = shipments
    .Where(s => s.Status == ShipmentStatus.Processing)
    .ToList();

The moment you call ToListAsync, the query is executed, and the rest happens in memory in your application.

The database sends every row over the network. EF Core materializes each row into an object. And then your code throws most of them away.

Keep the filter inside the IQueryable so it becomes a WHERE clause:

var pending = await dbContext.Shipments
    .Where(s => s.Status == ShipmentStatus.Processing
        && s.CreatedAt >= since)
    .ToListAsync(cancellationToken);

The same applies to sorting, grouping, and aggregation. Count(), Any(), Sum(), and Max() all translate to SQL when you call them on an IQueryable.
It all runs in memory when you call them after calling ToListAsync.

3. Project Only the Columns You Need

Loading a full entity means loading every column it maps to, including the ones your endpoint never returns.

If a Shipment has 10 columns and your list screen shows 4 of them, you're moving more than twice the data you need.

Project into a record or class instead:

public record ShipmentResponse(
    Guid Id,
    string Number,
    string Carrier,
    ShipmentStatus Status,
    DateTime CreatedAt);

var shipments = await dbContext.Shipments
    .Where(s => s.Status == ShipmentStatus.Processing)
    .OrderByDescending(s => s.CreatedAt)
    .Select(s => new ShipmentResponse(
        s.Id,
        s.Number,
        s.Carrier,
        s.Status,
        s.CreatedAt))
    .ToListAsync(cancellationToken);

EF Core translates the Select into a SELECT list with exactly those five columns.

Projections do three things at once:

• They cut the size of data the database reads
• They cut the size of data you transfer over the network from the database to your app
• They skip change tracking entirely, because there's no entity to track.

This is consistently the single biggest win available.
Projecting only the required columns can take your query somewhat from 10 seconds to 40 milliseconds (when reading 10,000 or more rows).

You can explore the benchmarks in the optimization challenge I did before with EF Core.

Projections also compose well with aggregation:

var summaries = await dbContext.Shipments
    .Where(s => s.CreatedAt >= since)
    .Select(s => new ShipmentSummary(
        s.Number,
        s.Status,
        s.Items.Count,
        s.Items.Sum(i => i.Quantity)))
    .ToListAsync(cancellationToken);

The database computes the item count and quantity sum. No ShipmentItem objects are created in your application at all.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/ef-core-optimization-guide-step-by-step