ExecuteUpdate and ExecuteDelete: Set-Based DML Without Loading or Tracking Entities

Learning outcomes

ServiceHub sometimes needs to change many rows without loading hundreds of entities—for example, append an operational marker to all work orders matching a narrow predicate, or purge disposable lab notes. ExecuteUpdate and ExecuteDelete express that as relational set-based DML, but they intentionally bypass the change tracker. That efficiency changes the correctness contract.

1. ExecuteUpdate/Delete are query-to-DML operators

var affected = await db.WorkOrders    .Where(w => EF.Property<DateTime>(w, "CreatedUtc") < cutoffUtc)    .ExecuteUpdateAsync(setters => setters        .SetProperty(w => w.Summary, w => w.Summary + " [legacy-review]"), ct);Console.WriteLine($"Rows updated: {affected}");

UPDATE "work_orders" AS "w"SET "summary" = "w"."summary" || ' [legacy-review]'WHERE "w"."created_utc" < @cutoffUtc;

No WorkOrder instance is loaded, tracked, or passed through domain methods. This is excellent when the database operation itself is the intended invariant-preserving operation; it is inappropriate when per-aggregate business rules must run in memory.

2. The deliberate trap: a tracked entity becomes stale

var tracked = await db.WorkOrders.SingleAsync(w => w.Id == id, ct);Console.WriteLine(tracked.Summary); // "Inspect pump"await db.WorkOrders    .Where(w => w.Id == id)    .ExecuteUpdateAsync(s => s        .SetProperty(w => w.Summary, "Updated directly in the database"), ct);Console.WriteLine(tracked.Summary); // still "Inspect pump"tracked.AdvanceRevision();await db.SaveChangesAsync(ct); // may now write based on stale tracked values/state

ExecuteUpdate does not synchronize CurrentValues, OriginalValues, or IsModified. The safest default is a separate short-lived context/unit of work for direct DML. If one context must continue, explicitly reload or clear/requery after understanding the consequences.

await using (var direct = await factory.CreateDbContextAsync(ct)){    await direct.WorkOrders        .Where(w => w.Id == id)        .ExecuteUpdateAsync(s => s.SetProperty(w => w.Summary, newSummary), ct);}await using var verify = await factory.CreateDbContextAsync(ct);var fresh = await verify.WorkOrders.AsNoTracking().SingleAsync(w => w.Id == id, ct);

3. ExecuteDelete is immediate and relational constraints still apply

var deleted = await db.Set<WorkOrderNote>()    .Where(n => n.WorkOrderId == orderId && n.Text.StartsWith("LAB:"))    .ExecuteDeleteAsync(ct);Console.WriteLine($"Rows deleted: {deleted}");

The database executes the DELETE immediately. FK restrictions/cascades from Chapter 05 still apply. EF does not load dependents to perform client-side cascade behavior; therefore a relationship configured as ClientCascade does not magically make an unloaded direct DELETE safe.

4. Several Execute* calls need an explicit transaction for atomicity

await using var tx = await db.Database.BeginTransactionAsync(ct);await db.Set<WorkOrderNote>()    .Where(n => n.WorkOrderId == orderId && n.Text.StartsWith("LAB:"))    .ExecuteDeleteAsync(ct);await db.WorkOrders    .Where(w => w.Id == orderId)    .ExecuteUpdateAsync(s => s        .SetProperty(w => w.Summary, "Lab notes purged"), ct);await tx.CommitAsync(ct);

Without the explicit transaction, each direct DML invocation is its own immediate database operation. Chapter 14 will cover transaction/isolation/retry semantics in depth; for now, the key point is that “I used one DbContext” is not an atomicity guarantee for multiple Execute* calls.

5. Concurrency becomes explicit rows-affected logic

var nextRevision = expectedRevision + 1;var affected = await db.WorkOrders    .Where(w => w.Id == id && w.Revision == expectedRevision)    .ExecuteUpdateAsync(s => s        .SetProperty(w => w.Summary, newSummary)        .SetProperty(w => w.Revision, nextRevision), ct);if (affected == 0){    throw new ConcurrencyConflictException(id, expectedRevision);}

ExecuteUpdate does not automatically apply the tracker's original concurrency token logic because there is no tracked original. Put the token in the predicate and interpret rows affected deliberately. Chapter 13 formalizes conflict-resolution strategies.

6. EF Core 10 adds more set-based update shapes, but providers still matter

EF Core 10 supports ExecuteUpdate over complex types mapped to relational JSON and makes dynamic setter construction easier. This is an EF10 capability, but the resulting JSON update SQL depends on the provider/database. The ServiceHub SQLite mandatory path should inspect actual SQL before assuming the same shape as SQL Server 2025 or PostgreSQL.

7. Hands-on lab

1. Reset the SQLite database and count candidate work orders with a no-tracking query.
2. Run a filtered ExecuteUpdateAsync; record rows affected and generated SQL.
3. Reproduce stale tracked state by tracking one row first; verify the CLR object does not change automatically.
4. Repair by using a fresh context, then repeat with ReloadAsync to observe the alternative.
5. Run a lab-only ExecuteDeleteAsync over notes with a restrictive predicate.
6. Wrap two direct operations in an explicit transaction and inject an exception before commit; verify rollback.
7. Implement Revision in the predicate and prove zero rows becomes a manual concurrency signal.

8. Production judgment and bridge

Choose ExecuteUpdate/Delete when the operation is naturally set-based and can preserve invariants directly in SQL. Prefer tracked SaveChanges for aggregate/domain behavior and automatic optimistic concurrency. Keep the two models in separate short-lived units of work unless you deliberately reload state. Lesson 4 broadens the decision beyond EF to native loaders and third-party bulk tooling.

Authoritative references

• ExecuteUpdate and ExecuteDelete - EF Core — immediate execution, tracking, transactions, concurrency, limitations.
• Saving Data - EF Core — comparison with SaveChanges.
• What’s New in EF Core 10 — JSON complex ExecuteUpdate and setter improvements.
• Using Transactions - EF Core — explicit transaction coordination.
• Handling Concurrency Conflicts - EF Core — optimistic concurrency concepts used by the rows-affected pattern.