CS Fundamentals Trainer · Guides

Entity Framework Core in practice: change tracking, N+1 queries and loading related data

How a DbContext tracks entities and turns changes into SQL on SaveChanges, when to use AsNoTracking, the N+1 problem and the four ways to load related data (Include, split queries, projection, explicit loading), concurrency tokens, migrations and the habits that keep queries predictable.

4 min read

Entity Framework Core (EF Core) maps C# classes to tables and translates LINQ into SQL. It saves a lot of code, and it also makes it easy to write a query that looks fine and runs hundreds of statements. This guide covers the behaviour that decides whether it is fast and correct.

The DbContext is a unit of work

A DbContext does three things: it translates queries, it tracks the entities it returned, and it writes the changes when you call SaveChanges. It is meant to be short-lived (one web request: register it as scoped) and it is not thread-safe.

var order = await db.Orders.SingleAsync(o => o.Id == id);   // loaded and tracked
order.Status = OrderStatus.Shipped;                          // change detected by comparing to a snapshot
await db.SaveChangesAsync();                                 // UPDATE Orders SET Status = ... WHERE Id = ...

SaveChanges runs all pending changes in one transaction. The generated UPDATE touches only the columns that changed.

Tracking and AsNoTracking

By default every entity a query returns is tracked, which costs memory and time (snapshots, identity map). For read-only queries use AsNoTracking() (or set it as the default for a context); you cannot save changes to untracked entities, but listing and reporting get noticeably cheaper. Tracking also gives you identity resolution: two queries that return the same row give the same object.

The N+1 problem

Loading a list and then touching a related property for each item runs one query for the list plus one per item.

var orders = await db.Orders.ToListAsync();
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name);   // with lazy loading: one query per order

Lazy loading (a proxy loads the relation when you touch it) hides this, which is why it is off by default and best left off. Without it, o.Customer would simply be null here. Four ways to load related data on purpose:

  1. Include: db.Orders.Include(o => o.Customer) loads both with a join.
  2. Split queries: AsSplitQuery() loads each collection with its own query, which avoids the cartesian explosion where joining two collections multiplies the rows (10 order lines x 10 payments = 100 rows per order).
  3. Projection: db.Orders.Select(o => new { o.Id, CustomerName = o.Customer.Name }) selects just the columns you need. It is usually the fastest, needs no tracking and cannot over-fetch.
  4. Explicit loading: await db.Entry(order).Collection(o => o.Lines).LoadAsync() loads one relation of an entity you already have.

Choose projection for read models and Include when you will modify the graph.

See the SQL

Turn on logging (LogTo(Console.WriteLine), or EnableSensitiveDataLogging in development) or use query.ToQueryString(). Count the statements per request. A page that issues dozens of near-identical queries has an N+1 problem, and a test that asserts the number of commands catches regressions.

Client evaluation and translation

EF Core translates the part of a query it can into SQL. If a Where calls your own method it cannot translate, newer versions throw instead of loading the whole table, but a Select at the end of a query may run in memory. Keep filters and ordering in translatable expressions, and call ToListAsync last (see the guide on LINQ and deferred execution).

Concurrency

Two users edit the same row: the last SaveChanges wins and silently overwrites the first. Add a concurrency token: a rowversion column (SQL Server) or xmin (PostgreSQL), or [ConcurrencyCheck] on a property. EF adds it to the WHERE of the update, and if no row matched it throws DbUpdateConcurrencyException, which you handle by reloading or merging.

Bulk work

SaveChanges is built for tens to a few thousand rows. Since EF Core 7, ExecuteUpdateAsync and ExecuteDeleteAsync run a set-based UPDATE/DELETE without loading entities, which is the right tool for "mark all expired rows". For very large imports, use the database's bulk-copy facility.

Migrations

dotnet ef migrations add Name compares the model to the last snapshot and writes a migration; dotnet ef database update applies it. Review generated SQL (dotnet ef migrations script) before production, and make migrations additive: add a nullable column, deploy the code that uses it, then tighten. Applying migrations at application start is convenient in development, but with several instances running you should run them as a separate deployment step.

Habits

  1. One context per request, never shared across threads.
  2. AsNoTracking or projection for reads.
  3. Log the SQL and count queries in tests.
  4. Always await the async methods (ToListAsync, SaveChangesAsync).
  5. Keep transactions short; do not call external services while one is open.
  6. Add indexes for the columns you filter and join on; EF creates them for keys and foreign keys only.

Sources

Practise this topic: C# & .NET C# & .NET › Databases

More guides