Entity Framework Core and performance

The problem: the orders page is slow

In our online shop, the admin panel has one page: “the last 50 orders, with the customer name”. The tables are small, but the page takes about 2 seconds. The code looks simple and clean:

var orders = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .ToListAsync(ct);

return orders.Select(o => new OrderRow(o.Id, o.Total, o.Customer.Name));

The problem is not visible in the C# code. The problem is in the SQL that EF Core builds. So the first skill in EF Core is this: know what query each line of code sends to the database.

How does the EF Core library work?

The DbContext class really does three jobs:

1. Translation. It turns LINQ code into SQL.
2. Building objects. It turns the result rows into C# objects.
3. Change Tracking. It keeps a copy of every object it reads. When saving, it compares the object with the copy and runs an UPDATE only for the changed columns.

One important point from this picture: as long as the query type is IQueryable, the work happens in the database. When you bring it into memory with ToListAsync or AsEnumerable, every later Where and Select runs in the app’s memory.

// Bad: every order comes into memory, then C# filters them
var bigOrders = db.Orders.AsEnumerable().Where(o => o.Total > 1_000_000).ToList();

// Good: the filter becomes a WHERE clause in SQL
var bigOrders2 = await db.Orders.Where(o => o.Total > 1_000_000).ToListAsync(ct);

To see the real SQL, turn on the EF Core log. In the development environment, this is enough:

builder.Services.AddDbContext<ShopDb>(o => o
    .UseSqlServer(builder.Configuration.GetConnectionString("Shop"))
    .LogTo(Console.WriteLine, LogLevel.Information));

The N+1 problem

Back to the orders page. In this project, Lazy Loading is on. This means EF Core reads the customer of each order at the moment the code first touches it. Step by step:

1. The ToListAsync method sends one query: 50 orders, without customers.
2. Then Select runs in memory and asks for the customer name of each order.
3. Each time, Lazy Loading sends a separate query for that one customer.
4. So there is 1 query for the orders and 50 queries for the customers. This is called N+1.
5. Each query has one network round trip. 51 round trips, one after another, make the page slow.

The same problem also happens without Lazy Loading. It is enough for a developer to run one query for each order inside a foreach loop.

Solution: Include or Projection

With Include, the customer comes with a JOIN in the same first query. But for a page that only shows data, the better way is Projection. This means you write Select before ToListAsync:

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

var rows = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .Select(o => new OrderRow(o.Id, o.Total, o.Customer.Name))
    .ToListAsync(ct);

Why is Projection better?

1. EF Core itself builds a JOIN from the Select.
2. Only the three needed columns are read, not all columns of both tables.
3. The OrderRow object is not an Entity. So the Change Tracker and Lazy Loading are not involved at all.

Press the buttons and see the number of queries:

Change tracking and AsNoTracking

When you want to change something, Tracking is a big help. Read the object, change it, and save it:

var order = await db.Orders.FirstAsync(o => o.Id == orderId, ct);
order.ShippingAddress = newAddress;
await db.SaveChangesAsync(ct); // UPDATE only the changed column

But when you only read, these copies are a useless cost. For each row, they take memory and comparison time. So for a read-only query, add the AsNoTracking method, or write a Projection directly.

One more point: the SaveChangesAsync method saves all changes in one transaction. So for a simple save, do not open a transaction yourself. The DbContext is itself a Unit of Work.

Bulk change without reading

If you want to mark all old unpaid orders as “expired”, you do not need to read them all first. The ExecuteUpdateAsync method sends an UPDATE command directly:

await db.Orders
    .Where(o => o.Status == OrderStatus.Pending && o.CreatedAt < cutoff)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, OrderStatus.Expired), ct);

This command does not go through the Change Tracker. This means that if one of these orders was already read in the same DbContext, the object in memory does not change.

Very large data: read it piece by piece

The shop manager wants an export of 2 million transactions. If you read all of them with ToListAsync, 2 million objects are in memory at the same time, and the Pod may die from lack of memory. Instead, read and write the rows one by one:

await foreach (var tx in db.Transactions.AsNoTracking().AsAsyncEnumerable().WithCancellation(ct))
{
    await writer.WriteRowAsync(tx, ct);
}

In this case, each row is read, written, and then leaves memory. AsNoTracking is also needed here. Otherwise the Change Tracker keeps all the objects, and the benefit of reading piece by piece is lost.

DbContext lifetime

1. One DbContext per request. The AddDbContext method registers it as Scoped. This is correct.
2. One DbContext is not safe for several threads. If you run two queries on one DbContext at the same time with Task.WhenAll, EF Core throws an InvalidOperationException error. For parallel work, create a separate DbContext for each job (for example, with IDbContextFactory).
3. Do not make the DbContext class a Singleton. Its change tracker keeps growing, and different requests see each other’s data.

Changing the database schema without Downtime

Now we want to turn the customer’s FullName column into two columns, FirstName and LastName. The service has 4 Pods, and the Deploy is Rolling. This means that for a few minutes, the old and new code work together.

If you write a Migration that creates the new columns and removes the old column at the same moment:

1. Three old Pods still read the FullName column and get a “column does not exist” error.
2. If the new version has a bug, a Rollback is also not possible. Because the old code needs a column that no longer exists.

The solution is the Expand and Contract pattern. First add the new thing, work with both for a while, and at the end remove the old one.

A few rules for Migration in Production:

1. Run the migration separately from the app start. If all 4 Pods call the Migrate method at startup, they may run at the same time. It is better to have a separate step in the Pipeline. With the command that builds a SQL script, or with a Migration Bundle, this is easy:

dotnet ef migrations script --idempotent -o migrate.sql
dotnet ef migrations bundle -o efbundle

2. Fill old data in batches. One UPDATE on millions of rows locks the table for a long time.
3. Read the Migration script before running it. Sometimes, for a rename, EF Core drops the column and creates it again, and the data is lost.

Do you need a Repository on top of EF Core?

The DbContext class is itself a Unit of Work, and each DbSet is like a Repository. So a generic Repository (with Add, Update and GetAll for all tables) is usually just an extra layer. Worse, it sometimes hides Include and Projection, and we get back to N+1.

But a specific Repository, for example for the order Aggregate, is useful in some places. For example, when repeated queries should live in one place, or when you want one point to add a cache (with the Decorator pattern).

Important rules

1. Always look at the generated SQL. With the log, or with the ToQueryString method.
2. For display, write a Projection. Read only the needed columns.
3. For reading, use AsNoTracking. Tracking is needed only when you want to save.
4. Write the filter before ToListAsync. So it runs in SQL, not in memory.
5. Be careful with Lazy Loading. Many teams keep it off so that N+1 does not stay hidden.
6. Migrations compatible with the previous version. Never remove a column in the same Deploy while the old code still reads it.

Common mistakes

When to use EF Core?