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.

P.S.: EF Core can be really slow when inserting data.
Depending on how many rows you insert and columns you have, it can take 5, 10 seconds, or even a minute. This amazing library offers the best performance for bulk write operations in the market. See it below.

All examples in this article use a shipment tracking domain:

csharp
1public class Shipment
2{
3    public Guid Id { get; set; }
4    public string Number { get; set; }
5    public string OrderId { get; set; }
6    public string Carrier { get; set; }
7    public string ReceiverEmail { get; set; }
8    public ShipmentStatus Status { get; set; }
9    public DateTime CreatedAt { get; set; }
10    public DateTime? UpdatedAt { get; set; }
11    
12    public Address Address { get; set; }
13    
14    public List<ShipmentItem> Items { get; set; }
15}
16
17public class ShipmentItem
18{
19    public Guid Id { get; set; }
20    public string Name { get; set; }
21    public int Quantity { get; set; }
22    
23    public Guid ShipmentId { get; set; }
24    public Shipment Shipment { get; set; }
25}

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:

csharp
1builder.Services.AddDbContext<ShipmentsDbContext>(options =>
2{
3    options.UseNpgsql(connectionString);
4
5    if (builder.Environment.IsDevelopment())
6    {
7        options.LogTo(Console.WriteLine, LogLevel.Information)
8            .EnableSensitiveDataLogging()
9            .EnableDetailedErrors();
10    }
11});

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:

csharp
1var shipment = await context.Shipments
2    .Include(x => x.Items)
3    .Where(s => s.Number == shipmentNumber)
4    .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.

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.

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):

csharp
1public class ShipmentConfiguration : IEntityTypeConfiguration<Shipment>
2{
3    public void Configure(EntityTypeBuilder<Shipment> builder)
4    {
5        builder.HasKey(x => x.Id);
6
7        builder.HasIndex(x => x.Number)
8            .IsUnique();
9
10        builder.HasIndex(x => new { x.Status, x.CreatedAt });
11    }
12}

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:

csharp
1builder.HasIndex(x => x.CreatedAt)
2    .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.

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

csharp
1// Loads every shipment in the table
2var shipments = await dbContext.Shipments.ToListAsync(cancellationToken);
3
4var pending = shipments
5    .Where(s => s.Status == ShipmentStatus.Processing)
6    .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:

csharp
1var pending = await dbContext.Shipments
2    .Where(s => s.Status == ShipmentStatus.Processing
3        && s.CreatedAt >= since)
4    .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.

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:

csharp
1public record ShipmentResponse(
2    Guid Id,
3    string Number,
4    string Carrier,
5    ShipmentStatus Status,
6    DateTime CreatedAt);
7
8var shipments = await dbContext.Shipments
9    .Where(s => s.Status == ShipmentStatus.Processing)
10    .OrderByDescending(s => s.CreatedAt)
11    .Select(s => new ShipmentResponse(
12        s.Id,
13        s.Number,
14        s.Carrier,
15        s.Status,
16        s.CreatedAt))
17    .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:

csharp
1var summaries = await dbContext.Shipments
2    .Where(s => s.CreatedAt >= since)
3    .Select(s => new ShipmentSummary(
4        s.Number,
5        s.Status,
6        s.Items.Count,
7        s.Items.Sum(i => i.Quantity)))
8    .ToListAsync(cancellationToken);

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

You're now fetching the right rows and the right columns. The next step is how EF Core hands them to you.

By default, EF Core tracks every entity a query returns.

It keeps a snapshot of the original values so it can work out what changed when you call SaveChanges. That snapshot costs memory and CPU on every single row.

For a query that only reads data, all of that work is thrown away.

csharp
1var shipments = await dbContext.Shipments
2    .AsNoTracking()
3    .Where(s => s.Status == ShipmentStatus.Delivered)
4    .ToListAsync(cancellationToken);

AsNoTracking skips the snapshot and returns the entities directly.

When your query loads the same entity more than once, through a graph or a join, use AsNoTrackingWithIdentityResolution() instead. It skips the change tracker but still returns one object per database row identity, so you don't get duplicate instances of the same shipment.

If most of your DbContext usage is reads (report module), flip the default and opt into tracking where you need it:

csharp
1options.UseNpgsql(connectionString)
2    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

One thing worth knowing: if you already project into a record with Select, you don't need AsNoTracking. There's nothing to track, and adding it changes nothing.

I go into more detail on how the change tracker works in Understanding Change Tracking for Better Performance in EF Core.

Include is how you load related entities in one round-trip. It's also how a small query turns into a large one.

csharp
1var shipments = await dbContext.Shipments
2    .Include(s => s.Items)
3    .Where(s => s.Status == ShipmentStatus.Processing)
4    .ToListAsync(cancellationToken);

This loads every column of every shipment and every column of every item.

If you only need the item names, project instead of including:

csharp
1var shipments = await dbContext.Shipments
2    .Where(s => s.Status == ShipmentStatus.Processing)
3    .Select(s => new ShipmentWithItems(
4        s.Number,
5        s.Items.Select(i => i.Name).ToList()))
6    .ToListAsync(cancellationToken);

When you genuinely need the entities, filter the include so you don't load the whole collection:

csharp
1var shipments = await dbContext.Shipments
2    .Include(s => s.Items.Where(i => i.Quantity > 0))
3    .ToListAsync(cancellationToken);

The bigger trap is lazy loading. With lazy loading proxies enabled, accessing shipment.Items inside a loop issues a separate query per shipment. A list of 500 shipments becomes 501 database round-trips, and nothing in your code makes that visible.

This is the N+1 problem, and it's the reason I don't enable lazy loading in production services.

Lazy loading is not recommended by Microsoft themselves. It only exists to make migration from old .NET Framework and Entity Framework 6 apps to new ASP .NET Core and EF Core apps easier.

Load what you need explicitly, and the query count stays something you can reason about.

When you include two or more collections, EF Core joins them into a single result set.

A shipment with 10 items and 10 status events returns 100 rows, and every shipment column is repeated across all 100 rows. This is called a Cartesian explosion, and it grows by multiplication.

AsSplitQuery tells EF Core to issue one query per collection instead:

csharp
1var shipments = await dbContext.Shipments
2    .Include(s => s.Items)
3    .Include(s => s.StatusEvents)
4    .AsSplitQuery()
5    .Where(s => s.CreatedAt >= since)
6    .ToListAsync(cancellationToken);

Now you get three small result sets with no duplication instead of one large one.

Split query isn't always faster.

It trades one round-trip for several. When your collections are small or the query is already selective, the extra round trips cost more than the duplicated rows.

In my optimization challenge, adding AsSplitQuery made the query slower, moving it from 40 milliseconds to 51 milliseconds.

Measure it on your data before you add it.

Note: split query runs multiple statements, so the results aren't guaranteed to be consistent with each other unless you wrap them in a transaction. For most read scenarios, this is fine, but it matters if the data changes underneath very frequently.

A query that's fast on 10,000 rows can fall over at 10 million. These two optimizations are about the queries that degrade as your table grows.

Offset pagination is the obvious approach and the one almost everyone starts with:

csharp
1var page = await dbContext.Shipments
2    .OrderByDescending(s => s.CreatedAt)
3    .Skip(pageNumber * pageSize)
4    .Take(pageSize)
5    .ToListAsync(cancellationToken);

Skip translates to OFFSET, and OFFSET isn't free.

To return rows 100,000 to 100,020, the database has to walk the first 100,000 rows and discard them. Even when using an index, it still needs to walk the first 100,000 rows in the index.

The danger is that you get results pretty fast for the first 100 pages. But page 5,000 can become so slow.

You may say: "Who scrolls to page 5,000 on UI?" And the answer is: "Those who click to jump straight to the last page."

Cursor or Keyset pagination asks for rows after a known position instead:

csharp
1var page = await dbContext.Shipments
2    .Where(s => s.CreatedAt < lastCreatedAt
3        || (s.CreatedAt == lastCreatedAt && s.Id < lastId))
4    .OrderByDescending(s => s.CreatedAt)
5    .ThenByDescending(s => s.Id)
6    .Take(pageSize)
7    .ToListAsync(cancellationToken);

The database seeks straight to the position in the index and reads 20 rows. Page 5,000 costs the same as page 1.

The Id comparison is there to break ties. If two shipments share a CreatedAt value, sorting by the timestamp alone isn't deterministic, and rows can be skipped or repeated across pages.

The trade-off is that you can't jump to a random page number, because each page needs the last row of the previous one. For infinite scroll-like "load more" or paging back/next, this is a perfect fit.

This one catches experienced developers, because the code looks completely reasonable:

csharp
1var shipments = await dbContext.Shipments
2    .Where(s => orderIds.Contains(s.OrderId))
3    .ToListAsync(cancellationToken);

Contains translates to a SQL IN clause with one parameter per element.

That works fine with 50 IDs. It breaks with 50,000, because every database puts a ceiling on how many parameters a single statement can take:

• Oracle: 1,000 expressions in an IN list
• SQL Server: 2,100 parameters per statement
• PostgreSQL and MySQL: much higher (around 64K), but the query planner slows down long before you reach the limit

The failure is nasty because it depends on data volume. The code works in development and tests, but fails in production when someone imports a large batch of orders.

The simplest fix is to batch the IDs and combine the results:

csharp
1const int batchSize = 500;
2var results = new List<Shipment>();
3
4foreach (var batch in orderIds.Chunk(batchSize))
5{
6    var chunk = await dbContext.Shipments
7        .AsNoTracking()
8        .Where(s => batch.Contains(s.OrderId))
9        .ToListAsync(cancellationToken);
10
11    results.AddRange(chunk);
12}

For genuinely large ID sets, a better approach is to get the IDs into the database as a table and join against them, using a temporary table or a table-valued parameter. A join against a table scales in a way that an IN list never will.

I wrote about the library approaches to this problem in EF Core Bulk Data Retrieval: 5 Methods You Should Know.

At this point, your queries fetch the right data efficiently. The remaining wins come from not doing the same work over and over.

Every time you execute a LINQ query, EF Core has to translate the expression tree into SQL.

EF Core caches this translation internally, so it's cheap after the first call. But on a hot path that runs thousands of times per second, even the cache lookup shows up in a profile.

A compiled query does the translation once, at startup:

csharp
1private static readonly Func<ShipmentsDbContext, string, Task<Shipment?>> GetByNumber =
2    EF.CompileAsyncQuery((ShipmentsDbContext context, string number) =>
3        context.Shipments
4            .AsNoTracking()
5            .FirstOrDefault(s => s.Number == number));
6
7public Task<Shipment?> GetShipmentAsync(string number) =>
8    GetByNumber(dbContext, number);

The delegate is static, so the translation is reused for the lifetime of the application.

Be honest about where this belongs. Compiled queries only pay off on queries you call very often with the same shape, and they can't be composed dynamically the way a normal IQueryable can. I reach for them on a handful of hot lookups, not across the codebase.

Caching is the last step.

Once a query is genuinely as fast as it can be and you're still running it more often than the data changes, caching the result is the right call.

In .NET 9 and later, HybridCache handles this well:

csharp
1public async Task<ShipmentResponse?> GetAsync(string number, CancellationToken ct)
2{
3    return await hybridCache.GetOrCreateAsync(
4        $"shipment:{number}",
5        async token => await dbContext.Shipments
6            .AsNoTracking()
7            .Where(s => s.Number == number)
8            .Select(s => new ShipmentResponse(
9                s.Id, s.Number, s.Carrier, s.Status, s.CreatedAt))
10            .FirstOrDefaultAsync(token),
11        cancellationToken: ct);
12}

HybridCache combines an in-memory cache with a distributed cache like Redis to solve cache stampede. When 100 requests ask for the same missing key at once, only one hits the database, and the rest wait for that result.

Invalidation is the hard part of caching. Cache data whose staleness you can live with, and be deliberate about evicting entries when the underlying rows change.

I've seen just as many production incidents caused by writes as by slow reads.

This pattern is common and very expensive (young developers and also AI sometimes make this mistake):

csharp
1foreach (var shipment in shipments)
2{
3    shipment.Status = ShipmentStatus.Dispatched;
4    await dbContext.SaveChangesAsync(cancellationToken);
5}

Each SaveChangesAsync opens a transaction, sends a statement, and waits for the database.

For 1,000 shipments, that's 1,000 round-trips, and network latency dominates everything else.

Move the call outside the loop:

csharp
1foreach (var shipment in shipments)
2{
3    shipment.Status = ShipmentStatus.Dispatched;
4}
5
6await dbContext.SaveChangesAsync(cancellationToken);

Now EF Core batches the updates into a small number of statements inside a single transaction. You also get correct behavior for free because either all shipments are dispatched or none are.

This works great if you have a few hundred up to 1,000 rows to insert (or update).

When it comes to thousands or tens of thousands of rows, you need a faster and more robust approach.

For inserting tens of thousands of rows, EF Core isn't the right tool even with batching, as it becomes pretty slow. That's where you need a dedicated bulk library. I recommend using EF Core extensions for bulk write operations.

When you need to update or delete data, you often load every entity into memory and then update it.

EF Core tracks the entities and compares snapshots to generate UPDATE or DELETE SQL statements when you call SaveChanges.

When you're applying the same change to many rows, you don't need any of that:

csharp
1await dbContext.Shipments
2    .Where(s => s.Status == ShipmentStatus.Processing
3        && s.CreatedAt < cutoff)
4    .ExecuteUpdateAsync(setters => setters
5        .SetProperty(s => s.Status, ShipmentStatus.Cancelled)
6        .SetProperty(s => s.UpdatedAt, DateTime.UtcNow),
7        cancellationToken);

This sends a single UPDATE ... WHERE statement. No rows are loaded, no entities are materialized, and no change tracking happens.

ExecuteDeleteAsync works the same way for deletes.

There are a few caveats:

• These methods bypass the change tracker, so entities already loaded in your DbContext will hold stale values.
• They also skip SaveChanges interceptors, which means audit trails and domain events wired into SaveChanges won't fire.

Use them for maintenance and bulk state transitions, and keep business-rule writes on SaveChanges.

I covered the details and the gotchas in The Correct Way to Use ExecuteUpdate and ExecuteDelete Methods in EF Core.

DbContext is designed to be short-lived. It's cheap to create, it isn't thread-safe, and it holds on to every entity it has tracked until it's disposed.

Registering it as scoped is suitable for most web applications, because the scope matches the request.

Background services and parallel work are where this breaks. A DbContext resolved once and reused across a long-running loop accumulates tracked entities and never releases them, which looks exactly like a memory leak.

Use a factory when you need to control the lifetime yourself:

csharp
1builder.Services.AddDbContextFactory<ShipmentsDbContext>(options =>
2    options.UseNpgsql(connectionString));
3
4// In a background service
5await using var context = await dbContextFactory.CreateDbContextAsync(ct);
6
7var pending = await context.Shipments
8    .AsNoTracking()
9    .Where(s => s.Status == ShipmentStatus.Created)
10    .ToListAsync(ct);

Each iteration gets a fresh context that's disposed when it goes out of scope.

For high-throughput services, AddDbContextPool reuses context instances instead of allocating new ones, resetting their state between uses. It's a real saving on hot paths, though it doesn't support injecting per-request state into your DbContext's constructor.

All 13 optimizations are a lot to keep in your head when an endpoint is timing out and someone is asking for a quick fix.

Here's how they compare:

The order I work through them is always the same.

Step 1:
I start by measuring because guessing takes more time than measuring.

Step 2:
Then I fix what the query fetches: indexes, filters, and projections, in that order. Those three account for most of the wins in every codebase I've worked on, and they're the cheapest changes to make.

Step 3:
Only then do I look at how the data is loaded, with tracking behavior and include strategy.

Step 4:
Pagination and parameter limits come next, because they're about how the query behaves as the table grows rather than how it behaves today.

Step 5:
Compiled queries and caching come last. Both add complexity, and both are much less useful once the first three steps have done their job. Caching an unoptimized query only reduces how often you pay its cost.

Hope you find this newsletter useful. See you next time.