Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 52 ยท Data Access

EF Core in Practice

Use EF Core well in web apps: AsNoTracking, fixing N+1 with Include, keyset pagination, concurrency tokens, transactions and DbContext lifetime.

Knowing what EF Core can do is only half the job. In a web application the same few mistakes cause most of the pain: queries that run hundreds of times for one page, tracking thousands of entities nobody will change, pages that skip or repeat rows while someone is adding data, two users silently overwriting each other, and half-finished updates after an error. This lesson is a field guide to those problems and their standard fixes.

The live runner cannot load EF Core (it is a NuGet package that needs a database), so the real EF Core code below is read-only. For each problem there is also a small runnable stand-in that reproduces the behavior with plain C#, so you can see the numbers and the failure modes. If you need the basics first, read Entity Framework Core Overview.

Do not track what you will not change

By default every entity a query returns is tracked: EF Core stores a snapshot and keeps a reference so that SaveChanges can find edits. For an endpoint that only reads, all of that is wasted memory and CPU. Turn it off with AsNoTracking():

C#
app.MapGet("/products", async (ShopContext db, CancellationToken ct) =>
    await db.Products
        .AsNoTracking()
        .OrderBy(p => p.Name)
        .Select(p => new ProductDto(p.Id, p.Name, p.Price))
        .ToListAsync(ct));

Two rules of thumb. A projection to a DTO (the Select above) is never tracked, because the result is not an entity, so it is already the cheapest option. And if a read-only query returns entities, add AsNoTracking(). You can also make no-tracking the default for a whole context with UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking) and opt back in with AsTracking() where you intend to edit. Without tracking, repeated rows give you separate object instances (no identity map), which is normally fine for reads.

The N+1 problem and Include

The most common performance bug with ORMs is N+1 queries: one query to get a list, then one more query per item to get something related. It is easy to write by accident, especially when a loop touches a navigation property or calls another repository method.

C#
// 1 query for orders, then 1 query per order for its customer: N+1
var orders = await db.Orders.ToListAsync(ct);
foreach (var order in orders)
{
    var customer = await db.Customers.FindAsync(order.CustomerId);   // a query each time (when not tracked yet)
    Console.WriteLine($"{order.Id}: {customer!.Name}");
}

With 8 orders that is 9 round trips; with 800 it is 801, and each trip costs network latency. The fixes are to let the database join:

C#
// Eager loading: one query with a JOIN
var orders = await db.Orders
    .Include(o => o.Customer)
    .ToListAsync(ct);

// Better when you do not need the entities: project exactly what you show
var rows = await db.Orders
    .Select(o => new { o.Id, o.Total, CustomerName = o.Customer!.Name })
    .ToListAsync(ct);

Include loads related entities in the same query, ThenInclude goes one level deeper, and a projection fetches only the columns you list. Lazy loading (proxies that fetch navigations on first access) hides the N+1 pattern inside property reads, which is why many teams leave it switched off.

flowchart LR
    subgraph bad["N+1"]
        A1["SELECT orders"] --> A2["SELECT customer 1"]
        A1 --> A3["SELECT customer 2"]
        A1 --> A4["... one per order"]
    end
    subgraph good["Include or projection"]
        B1["SELECT orders<br/>JOIN customers"]
    end

The same data costs one round trip instead of N+1.

C# Counting round trips: N+1 against a join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Include of several collections in one query makes the database return the cross product of the rows (a "cartesian explosion"): an order with 20 lines and 10 payments produces 200 rows. If you see slow queries with many collection includes, add .AsSplitQuery() so EF Core issues one query per collection instead, or project only what you need.

Pagination that stays correct

Never return an unbounded list. The usual first attempt is offset paging: Skip((page - 1) * size).Take(size). It is simple and allows "jump to page 7", but it has two weaknesses. The database must walk past every skipped row, so deep pages get slower, and when rows are inserted or deleted while the user is paging, items can shift between pages and appear twice or not at all.

Keyset paging (also called cursor or seek paging) asks for "the next rows after the last one I saw", which an index can answer without scanning the skipped rows:

C#
// Offset: simple, but slow on deep pages and unstable under inserts
var page = await db.Posts.OrderByDescending(p => p.Id)
    .Skip((pageNumber - 1) * pageSize).Take(pageSize).ToListAsync(ct);

// Keyset: the client sends the last id it saw
var next = await db.Posts
    .Where(p => p.Id < lastSeenId)
    .OrderByDescending(p => p.Id)
    .Take(pageSize)
    .ToListAsync(ct);

Always include a unique tie-breaker in the sort (the id). If you order by something non-unique such as a date, order by (Date, Id) and filter on the pair. Also return the total only when you really need it, because CountAsync on a large table is its own expensive query.

C# Offset paging against keyset paging

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Concurrency tokens: no silent overwrites

Two users open the same record. Both change it. Without protection the second save quietly overwrites the first (the lost update). EF Core offers optimistic concurrency: you mark a property as a concurrency token, EF Core includes it in the WHERE clause of every UPDATE and DELETE, and if no row matches, someone else changed the row in between and EF Core throws DbUpdateConcurrencyException.

C#
public class Account
{
    public int Id { get; set; }
    public decimal Balance { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = [];   // SQL Server rowversion, changes on every update
}

// What EF Core sends:
// UPDATE accounts SET balance = @p0 WHERE id = @p1 AND row_version = @p2;
// 0 rows affected  ->  DbUpdateConcurrencyException

For databases without a rowversion type you can use IsConcurrencyToken() on a normal property (a version number you increment yourself, or a Guid). Then you decide what to do in a catch: tell the user and reload (most common for forms), or re-read the current values, reapply the change and retry (suitable for increments such as stock or balance). In an API you typically return 409 Conflict, or 412 Precondition Failed when the client sent an ETag that no longer matches.

C# Lost update against a concurrency token

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Transactions

Every call to SaveChanges runs in its own transaction, so all the changes it contains succeed or fail together. That covers most cases. When one business operation needs several SaveChanges calls (or mixes EF Core with raw SQL), start an explicit transaction:

C#
await using var tx = await db.Database.BeginTransactionAsync(ct);

var from = await db.Accounts.FindAsync(fromId);
var to = await db.Accounts.FindAsync(toId);

from!.Balance -= amount;
await db.SaveChangesAsync(ct);

to!.Balance += amount;
await db.SaveChangesAsync(ct);

await tx.CommitAsync(ct);   // without this line, disposing the transaction rolls everything back

The rule is easy to remember: no Commit, no change. If an exception leaves the using scope, the transaction is rolled back. The next stand-in implements exactly that contract.

If you enable EnableRetryOnFailure() for transient database errors, EF Core may re-run your code, so wrap an explicit transaction in db.Database.CreateExecutionStrategy().ExecuteAsync(...) and keep the code inside idempotent.

C# Commit, or roll back on dispose

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Bulk operations and raw SQL

Loading entities to change them one by one is wasteful for set-based work. ExecuteUpdateAsync and ExecuteDeleteAsync translate a LINQ filter into a single UPDATE or DELETE statement without loading or tracking anything:

C#
int changed = await db.Products
    .Where(p => p.Category == "Books")
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m), ct);

int removed = await db.Sessions
    .Where(s => s.ExpiresAt < DateTime.UtcNow)
    .ExecuteDeleteAsync(ct);

These bypass change tracking and concurrency tokens, so tracked entities in the same context become stale. When you need hand-written SQL, use the interpolated forms, which turn each into a parameter:

C#
// Safe: category becomes a parameter
var books = await db.Products.FromSql($"SELECT * FROM products WHERE category = {category}").ToListAsync(ct);

// Dangerous: string concatenation makes SQL injection possible
var bad = db.Products.FromSqlRaw("SELECT * FROM products WHERE category = '" + category + "'");

DbContext lifetime and other web-app habits

  • One context per request (scoped, the AddDbContext default). Do not share one across threads, and never run two operations on the same context at once (Task.WhenAll over one context throws).
  • Long-lived services such as hosted background services and interactive Blazor components cannot use the request scope; inject IDbContextFactory<ShopContext> and create a short-lived context per unit of work.
  • Context pooling (AddDbContextPool) reuses context instances to cut allocation cost on very busy apps; it requires that contexts hold no per-request state of their own.
  • Always pass CancellationToken from the endpoint into every async EF call.
  • Look at the SQL. In development, options.LogTo(Console.WriteLine, LogLevel.Information) shows each statement; EnableSensitiveDataLogging() adds parameter values and belongs only in development.
  • Return DTOs, not entities from endpoints, as covered in DTOs, Records and Mapping.
  • Index what you filter and sort by, in the model (HasIndex) and verify with the database's query plan: EF Core cannot make a missing index fast.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. An endpoint only reads products and returns them as JSON. What should you add to the EF Core query?

    Read-only queries do not need change tracking. AsNoTracking avoids the snapshot and bookkeeping cost, and a projection to a DTO fetches only the needed columns and is never tracked.
  2. 2. You loop over 200 orders and each iteration runs a query for the order's customer. What is this called and how do you fix it?

    One query for the list plus one per item is the N+1 problem. Eager loading with Include, or a Select projection with a join, fetches everything in a single round trip.
  3. 3. Why can offset pagination (Skip/Take) show the same item on two pages?

    Offsets count positions, so when a row is inserted at the front the item that was last on page 1 becomes first on page 2. Keyset paging asks for rows after a specific key and stays stable.
  4. 4. What happens when a row with a concurrency token was changed by someone else before your SaveChanges?

    The token is part of the WHERE clause. If it no longer matches, no row is updated and EF Core raises DbUpdateConcurrencyException so you can reload, retry or report a conflict.
  5. 5. Inside an explicit transaction, an exception is thrown before CommitAsync is called. What happens to the changes?

    A transaction that is disposed without Commit is rolled back, so all changes made inside it are undone together.

Summary

  • Use AsNoTracking() (or a projection to a DTO) for read-only queries.
  • N+1 queries come from loading related data per item; fix them with Include, ThenInclude or a projection, and use AsSplitQuery for several collection includes.
  • Prefer keyset paging for large or changing data; offset paging is fine for small, stable sets. Always sort by a unique key.
  • Concurrency tokens turn lost updates into DbUpdateConcurrencyException; reload and retry, or return 409.
  • SaveChanges is atomic; use explicit transactions for multi-step operations, and remember that no Commit means rollback.
  • ExecuteUpdateAsync/ExecuteDeleteAsync do set-based work; FromSql with interpolation is parameterized, concatenation is not.
  • One scoped context per request; use IDbContextFactory in long-lived services; pass cancellation tokens everywhere.

Next: Caching: Memory, Distributed and Output