ExecuteUpdate and ExecuteDelete

Update and delete rows with one set-based statement instead of loading entities, and know which SaveChanges guarantees you give up.

Syntaxawait db.Products.Where(p => p.CategoryId == id) .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.05m), ct); await db.Carts.Where(c => c.LastTouchedAt < cutoff).ExecuteDeleteAsync(ct);

ExecuteUpdateAsync and ExecuteDeleteAsync translate a LINQ query straight into one UPDATE or DELETE statement and run it immediately. Nothing is loaded and nothing is tracked; the return value is the number of rows the database reports as affected. Cancelling 40,000 stale unpaid orders becomes one statement instead of 40,000 materialised entities followed by batched UPDATEs from SaveChanges.

How it is translated

The Where becomes the WHERE clause, and global query filters on the entity apply to it too, so a soft-delete or tenant filter also limits the UPDATE. Each SetProperty becomes one assignment. A constant or captured value is sent as a parameter; a lambda such as p => p.Price * 1.05m is translated into SQL that reads the current column value, so the arithmetic happens inside the database, atomically per row. That makes Stock = Stock - 3 with Where(p => p.Stock >= 3) a race-free reservation.

In EF Core 10 the setters argument is an ordinary lambda over an UpdateSettersBuilder, not an expression tree, so the SET list can be built with plain if statements: set the price only when the request carries one. Earlier versions needed hand-built expression trees for that.

What it does not do

SaveChangesExecuteUpdate / ExecuteDelete
Loads rowsYes, you load them firstNo
Tracked instancesUpdated to matchKeep their old values
SaveChanges overrides and SaveChangesInterceptorRunDo not run
Concurrency tokensChecked automaticallyOnly if you put the token in the Where
Cascade deleteEF deletes tracked dependents, then the database rule appliesOnly the database's ON DELETE rule
TransactionIts own, or the current oneOne statement; join an explicit transaction to combine it with other work

Database-maintained tokens still change: SQL Server bumps rowversion and PostgreSQL writes a new xmin on every updated row, so clients holding old ETags correctly get a conflict later.

Production failure modes

  • Stale tracked entities. If the same context already loaded an order, it still holds the old values after the bulk update. A later SaveChanges writes only the properties you modified, but anything computed from the stale copy is wrong. Run bulk statements before loading, or call ChangeTracker.Clear().
  • Missing audit columns. The interceptor that stamps UpdatedAt never sees these statements. Set those columns in the setters yourself.
  • One giant statement. An UPDATE over five million rows holds its locks until it finishes, can push SQL Server to escalate to a table lock (at roughly 5,000 locks on one object), grows the transaction log or WAL and lags replicas. Work in chunks of a few thousand keys until the count comes back 0.
  • A missing Where. db.Orders.ExecuteDeleteAsync() deletes every row the filters allow. Review these calls as carefully as raw SQL.

Example

Example · csharp
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<CatalogDb>(o => o.UseNpgsql(builder.Configuration.GetConnectionString("Catalog")));
builder.Services.AddSingleton(TimeProvider.System);
builder.Services.AddAuthentication().AddJwtBearer();
builder.Services.AddAuthorizationBuilder()
    .AddPolicy("CatalogAdmin", p => p.RequireRole("catalog-admin"));
var app = builder.Build();

// Mass changes are never anonymous: one call can reprice a whole category.
var admin = app.MapGroup("").RequireAuthorization("CatalogAdmin");

// PATCH a whole category: only the fields present in the request end up in the SET clause.
admin.MapPatch("/categories/{categoryId:int}/products",
    async Task<Results<Ok<BulkResult>, ValidationProblem>> (
        int categoryId, BulkProductChange change, CatalogDb db, TimeProvider clock, CancellationToken ct) =>
{
    if (change.PriceMultiplier is null && change.Discontinued is null)
    {
        // Without this, the UPDATE would only touch UpdatedAt and bump every row version for nothing.
        return TypedResults.ValidationProblem(new Dictionary<string, string[]>
        {
            [""] = ["Send priceMultiplier, discontinued or both."]
        });
    }

    if (change.PriceMultiplier is <= 0m or > 2m)
    {
        return TypedResults.ValidationProblem(new Dictionary<string, string[]>
        {
            ["priceMultiplier"] = ["Must be greater than 0 and at most 2."]
        });
    }

    var now = clock.GetUtcNow();
    var updated = await db.Products
        .Where(p => p.CategoryId == categoryId && !p.Discontinued)
        .ExecuteUpdateAsync(s =>
        {
            if (change.PriceMultiplier is decimal multiplier)
                s.SetProperty(p => p.Price, p => Math.Round(p.Price * multiplier, 2));
            if (change.Discontinued is bool discontinued)
                s.SetProperty(p => p.Discontinued, discontinued);
            // No interceptor sees this statement, so the audit column is set here.
            s.SetProperty(p => p.UpdatedAt, now);
        }, ct);

    return TypedResults.Ok(new BulkResult(updated));
});

// A maintenance job: better run from a BackgroundService on a schedule; if exposed, admin only.
admin.MapDelete("/carts/expired", async (CatalogDb db, TimeProvider clock, CancellationToken ct) =>
{
    var cutoff = clock.GetUtcNow().AddDays(-30);
    var deleted = await db.Carts.Where(c => c.LastTouchedAt < cutoff).ExecuteDeleteAsync(ct);
    return TypedResults.Ok(new BulkResult(deleted));
});

app.Run();

public record BulkProductChange(decimal? PriceMultiplier, bool? Discontinued);
public record BulkResult(int Rows);

public class Product
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public string Sku { get; set; } = "";
    public decimal Price { get; set; }
    public bool Discontinued { get; set; }
    public DateTimeOffset UpdatedAt { get; set; }
}

public class Cart
{
    public Guid Id { get; set; }
    public DateTimeOffset LastTouchedAt { get; set; }
}

public class CatalogDb(DbContextOptions<CatalogDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Cart> Carts => Set<Cart>();
}

When to use it

  • Cancel every unpaid order older than 7 days with one UPDATE from a nightly job instead of loading 40,000 entities.
  • Reserve stock with Where(OnHand - Reserved >= qty) and an increment setter so two checkouts cannot sell the last unit twice.
  • Delete audit rows older than 400 days in chunks of 4,000 so the purge never escalates to a table lock during business hours.

More examples

Atomic stock reservation without a lost update

Loading the item, checking <code>OnHand - Reserved</code> in C# and saving is a read-modify-write race: two requests read 1 available unit and both reserve it. Here the database evaluates the condition and the increment under the row lock of a single UPDATE, so the second request sees the first one's change and matches 0 rows. Map 0 rows to a 409 response. No concurrency token is needed because nothing was read beforehand.

Example · csharp
using Microsoft.EntityFrameworkCore;

public class InventoryItem
{
    public int Id { get; set; }
    public string Sku { get; set; } = "";
    public int OnHand { get; set; }
    public int Reserved { get; set; }
}

public class WarehouseDb(DbContextOptions<WarehouseDb> options) : DbContext(options)
{
    public DbSet<InventoryItem> Inventory => Set<InventoryItem>();
}

public enum ReserveOutcome { Reserved, InsufficientStock }

public class StockReservations(WarehouseDb db)
{
    public async Task<ReserveOutcome> ReserveAsync(string sku, int quantity, CancellationToken ct)
    {
        // The check and the write are one statement, so two concurrent reservations
        // for the last unit cannot both succeed.
        var rows = await db.Inventory
            .Where(i => i.Sku == sku && i.OnHand - i.Reserved >= quantity)
            .ExecuteUpdateAsync(s => s.SetProperty(i => i.Reserved, i => i.Reserved + quantity), ct);

        return rows == 1 ? ReserveOutcome.Reserved : ReserveOutcome.InsufficientStock;
    }
}

Purging in chunks

<code>OrderBy</code> plus <code>Take</code> before <code>ExecuteDeleteAsync</code> is translated into a DELETE whose WHERE is a key subquery with a LIMIT, so each statement touches at most 4,000 rows. Every chunk commits on its own: locks are short, SQL Server stays under its lock escalation threshold, and replicas keep up. The pause between chunks leaves room for normal traffic. An index on <code>RecordedAt</code> keeps each chunk from scanning the table.

Example · csharp
using Microsoft.EntityFrameworkCore;

public class AuditEntry
{
    public long Id { get; set; }
    public DateTimeOffset RecordedAt { get; set; }
    public string Action { get; set; } = "";
}

public class AuditDb(DbContextOptions<AuditDb> options) : DbContext(options)
{
    public DbSet<AuditEntry> Entries => Set<AuditEntry>();
}

public class AuditRetention(AuditDb db, TimeProvider clock, ILogger<AuditRetention> logger)
{
    private const int ChunkSize = 4_000;

    public async Task<int> PurgeAsync(CancellationToken ct)
    {
        var cutoff = clock.GetUtcNow().AddDays(-400);
        var total = 0;
        while (true)
        {
            // DELETE ... WHERE "Id" IN (SELECT "Id" ... ORDER BY "Id" LIMIT 4000)
            var deleted = await db.Entries
                .Where(e => e.RecordedAt < cutoff)
                .OrderBy(e => e.Id)
                .Take(ChunkSize)
                .ExecuteDeleteAsync(ct);

            total += deleted;
            if (deleted < ChunkSize) break;
            await Task.Delay(TimeSpan.FromMilliseconds(200), ct);
        }
        logger.LogInformation("Purged {Count} audit entries older than {Cutoff}", total, cutoff);
        return total;
    }
}

Combining a bulk update with SaveChanges atomically

On its own, <code>ExecuteUpdateAsync</code> commits immediately; if the following <code>SaveChangesAsync</code> failed, the orders would stay on hold with no note explaining why. The explicit transaction makes both writes one unit. It is wrapped in the execution strategy because a retrying strategy (<code>EnableRetryOnFailure</code>) rejects user-initiated transactions otherwise, and <code>ChangeTracker.Clear()</code> stops a retried attempt from inserting the note twice.

Example · csharp
using Microsoft.EntityFrameworkCore;

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Status { get; set; } = "Pending";
}

public class CustomerNote
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Text { get; set; } = "";
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<CustomerNote> Notes => Set<CustomerNote>();
}

public class CustomerSuspension(ShopDb db)
{
    public Task SuspendAsync(int customerId, string reason, CancellationToken ct)
    {
        var strategy = db.Database.CreateExecutionStrategy();
        return strategy.ExecuteAsync(async () =>
        {
            db.ChangeTracker.Clear();
            await using var tx = await db.Database.BeginTransactionAsync(ct);

            await db.Orders
                .Where(o => o.CustomerId == customerId && o.Status == "Pending")
                .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, "OnHold"), ct);

            db.Notes.Add(new CustomerNote { CustomerId = customerId, Text = $"Suspended: {reason}" });
            await db.SaveChangesAsync(ct);

            await tx.CommitAsync(ct);
        });
    }
}

Related

net ref executeupdatenet ref executedeletenet efcore change trackingnet efcore transactionsnet efcore concurrency

Discussion

  • Be the first to comment on this lesson.