skip to content

To archive millions of old sensor readings in EF Core 10, why use ExecuteUpdateAsync or ExecuteDeleteAsync instead of load-modify-SaveChanges, and what do they bypass?

level: middleimportance: must knowfreq 57%

answer

  1. one UPDATE or DELETE, no materialization
  2. SetProperty on UpdateSettersBuilder
  3. returns rows affected as int
  4. change tracker never told
  5. EF 10: plain lambda, conditional setters

basics

~20 s

ExecuteUpdateAsync and ExecuteDeleteAsync turn a LINQ query into one SQL UPDATE or DELETE that runs immediately, without loading or tracking rows, and return the affected row count. They bypass the change tracker, SaveChanges, its concurrency checks and any implicit transaction.

solid answer

~40 s

Load-modify-save for millions of readings means querying them, materializing and tracking each one, then sending a statement per row. `db.SensorReadings.Where(r => r.RecordedAt < cutoff).ExecuteUpdateAsync(s => s.SetProperty(r => r.IsArchived, true))` sends **one** `UPDATE ... WHERE` and returns the number of rows changed; `ExecuteDeleteAsync()` does the same with `DELETE`. Global query filters on the entity still apply to the `WHERE`. What they bypass: they execute **immediately**, not at `SaveChanges`; the change tracker is never updated, so tracked readings keep stale values and a later `SaveChanges` can overwrite the bulk change; there is no automatic concurrency check; and several calls are not wrapped in a transaction unless I start one. In EF Core 10 the setter argument is a plain `Action<UpdateSettersBuilder<T>>`, so I can add `SetProperty` calls conditionally inside a block.

code

csharp · 10 lines
csharp
app.MapPut("/readings/{id:long}", async (long id, CorrectionDto dto, TelemetryDbContext db, CancellationToken ct) =>
{
    var updated = await db.SensorReadings
        .Where(r => r.Id == id && r.Version == dto.Version)
        .ExecuteUpdateAsync(s => s
            .SetProperty(r => r.Value, dto.Value)
            .SetProperty(r => r.Version, r => r.Version + 1), ct);

    return updated == 0 ? Results.Conflict() : Results.NoContent();
});

go deeper

for a junior

Know that ExecuteUpdateAsync and ExecuteDeleteAsync change many rows with one SQL statement and do not need SaveChanges.

for a middle

Explain SetProperty's value and expression forms, the returned row count, and why tracked entities go stale after a bulk update.

for a senior

Design bulk jobs with explicit transactions, concurrency checks through the row count, filter awareness and no mixing with tracked edits.

for a principal

Decide which data invariants live in SaveChanges logic and therefore need a database-side or job-level answer once bulk statements bypass it.

## The archiving job The IoT platform flags readings older than 90 days as archived each night and deletes those older than two years. The tracked approach looks like this: ```csharp await foreach (var r in db.SensorReadings.Where(r => r.RecordedAt < cutoff).AsAsyncEnumerable()) r.IsArchived = true; await db.SaveChangesAsync(); ``` It reads every matching row, materializes and tracks an object per reading, detects changes, and sends an `UPDATE` per row (batched, but still one per row). For millions of readings that is slow and memory-hungry. ## The set-based alternative `ExecuteUpdate`, `ExecuteUpdateAsync`, `ExecuteDelete` and `ExecuteDeleteAsync` are extension methods on `IQueryable<T>` (in EF Core 10 they live in `EntityFrameworkQueryableExtensions`): ```csharp var archived = await db.SensorReadings .Where(r => r.RecordedAt < cutoff && !r.IsArchived) .ExecuteUpdateAsync(s => s .SetProperty(r => r.IsArchived, true) .SetProperty(r => r.ArchivedAt, DateTime.UtcNow), ct); var deleted = await db.SensorReadings .Where(r => r.RecordedAt < purgeBefore) .ExecuteDeleteAsync(ct); ``` With Npgsql the first call sends a single `UPDATE "SensorReadings" AS s SET ... WHERE ...`, the second a single `DELETE FROM "SensorReadings" AS s WHERE ...`. Each returns an `int`: the number of rows affected. `SetProperty` has two forms: - a value: `SetProperty(r => r.IsArchived, true)`, sent as a parameter; - an expression over the current row: `SetProperty(r => r.Value, r => r.Value * 0.1)`, translated into SQL that reads the old value. ## EF Core 10: setters are a plain lambda Before EF Core 10, the setters argument was an **expression tree**, so adding a setter only when a condition held meant building expressions by hand. In EF Core 10 it is an `Action<UpdateSettersBuilder<TSource>>`, so ordinary code works: ```csharp await db.SensorReadings.Where(r => r.DeviceId == id).ExecuteUpdateAsync(s => { s.SetProperty(r => r.IsArchived, true); if (clearValues) s.SetProperty(r => r.Value, 0); }, ct); ``` Code that built `Expression<Func<...>>` setters for EF 8 or 9 must be rewritten when upgrading. EF Core 10 also allows `ExecuteUpdate` on JSON columns, but only for types mapped as complex types. ## Projecting before updating `SetProperty` cannot follow navigations directly, but you can project first and update through the projection. To store each device's latest value on the device row: ```csharp await db.Devices .Select(d => new { Device = d, Last = d.Readings.OrderByDescending(r => r.RecordedAt).Select(r => r.Value).FirstOrDefault() }) .ExecuteUpdateAsync(s => s.SetProperty(x => x.Device.LastValue, x => x.Last), ct); ``` EF translates the projection into a correlated subquery inside a single `UPDATE`. Only one table is modified per statement, because databases generally allow `UPDATE` and `DELETE` to target a single table. ## What they bypass | Behaviour | `SaveChanges` path | `ExecuteUpdate` / `ExecuteDelete` | |---|---|---| | When it runs | at `SaveChanges` | immediately | | Change tracker | updated | not informed at all | | Concurrency tokens | checked automatically | only if you add them to `Where` and check the count | | Transaction | one per `SaveChanges` | one per call; none spanning calls unless you start it | | `SaveChanges` events and interceptors | run | not involved | | Global query filters | apply to queries | apply to the `WHERE` | The consequences: 1. **Stale tracked entities.** A reading loaded before the bulk update still holds its old values. If you then change it and call `SaveChangesAsync`, EF compares against its original snapshot and may overwrite the bulk change. Avoid mixing both styles in one context, or call `ChangeTracker.Clear()` afterwards. 2. **Transactions.** Two `ExecuteUpdateAsync` calls are two independent statements; wrap them in `BeginTransactionAsync` if both must succeed together. 3. **Database cascades still happen.** `ExecuteDelete` relies on the database's own `ON DELETE` rules; EF's in-memory cascade for tracked entities does not run. 4. **Filters.** A soft-delete or tenant filter narrows the rows affected; use `IgnoreQueryFilters()` deliberately when a job must touch every row.

  • How do you get optimistic concurrency with ExecuteUpdateAsync?
    Put the concurrency token into the `Where`, for example `r.Id == id && r.Version == expectedVersion`, and check the returned row count. Zero means another writer changed the row first. EF does not do this automatically because the method never consults the change tracker.
  • Do two ExecuteDeleteAsync calls in a row run in one transaction?
    No. Each call is a separate statement with its own implicit transaction. If the second fails, the first has already committed. Start a transaction with `Database.BeginTransactionAsync()` and commit after both calls to make them atomic.

  • ExecuteUpdateAsync changes are written when SaveChanges is called.
  • ExecuteDeleteAsync also removes the deleted entities from the change tracker.
  • ExecuteUpdate ignores global query filters.
  • ExecuteUpdate checks concurrency tokens automatically.
  • ExecuteUpdate needs RelationalQueryableExtensions and an expression-tree setter in EF Core 10.

Assumes EF Core 10, where ExecuteUpdate/ExecuteUpdateAsync take Action<UpdateSettersBuilder<T>> (EF Core 8 and 9 took an expression tree) and live in EntityFrameworkQueryableExtensions.