klod68/bulk-operation-batching

---

name: bulk-operation-batching description: "Use when performing batch inserts, updates, or deletes on large datasets, running scheduled cleanup or archive jobs, or when EF Core's default SaveChangesAsync is generating thousands of individual SQL statements causing timeouts or lock contention. Covers EF Core 7+ ExecuteUpdate/ExecuteDelete for single-statement bulk ops, chunked SaveChanges with ChangeTracker.Clear to prevent memory growth, and BulkExtensions for high-volume import/upsert scenarios. Domain: Data Access, Performance. Level: Intermediate. Tags: bulk, batch, ef-core, performance, execute-update, execute-delete."

Bulk Operation Batching

Problem

EF Core's default SaveChangesAsync generates one SQL statement per entity. Updating 10,000 rows means 10,000 UPDATE statements — causing timeout, lock contention, and excessive network round-trips.

Solution: Bulk Operations

Option A: EF Core 7+ ExecuteUpdate / ExecuteDelete (No Tracking)

// Bulk update — single SQL statement, no entity loading
await dbContext.Orders
    .Where(o => o.Status == OrderStatus.Pending && o.CreatedAtUtc < cutoff)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(o => o.Status, OrderStatus.Expired)
        .SetProperty(o => o.UpdatedAtUtc, DateTime.UtcNow),
    ct);

// Bulk delete — single SQL statement
await dbContext.Orders
    .Where(o => o.Status == OrderStatus.Expired && o.CreatedAtUtc < archiveCutoff)
    .ExecuteDeleteAsync(ct);

Option B: Chunked SaveChanges (When Tracking Is Needed)

public static async Task SaveInBatchesAsync<T>(
    DbContext dbContext,
    IReadOnlyList<T> entities,
    int batchSize = 500,
    CancellationToken ct = default) where T : class
{
    ArgumentNullException.ThrowIfNull(dbContext);
    ArgumentNullException.ThrowIfNull(entities);

    for (var i = 0; i < entities.Count; i += batchSize)
    {
        var batch = entities.Skip(i).Take(batchSize);
        dbContext.Set<T>().AddRange(batch);
        await dbContext.SaveChangesAsync(ct);
        dbContext.ChangeTracker.Clear(); // Prevent memory growth
    }
}

Option C: BulkExtensions (Third-Party, Maximum Performance)

// EFCore.BulkExtensions package
await dbContext.BulkInsertAsync(orders, cancellationToken: ct);
await dbContext.BulkUpdateAsync(orders, cancellationToken: ct);
await dbContext.BulkDeleteAsync(orders, cancellationToken: ct);

// Upsert (insert or update)
await dbContext.BulkInsertOrUpdateAsync(orders, cancellationToken: ct);

Decision Matrix

| Scenario | Approach | Why | |----------|----------|-----| | Update/delete by condition (no entity needed) | ExecuteUpdate/ExecuteDelete | Single SQL, no loading, fastest | | Insert 100–1,000 rows | Chunked SaveChangesAsync | EF tracks entities, moderate performance | | Insert/update 10,000+ rows | BulkExtensions | Optimized SQL, minimal round-trips | | Import from external source | BulkExtensions or raw SQL | Volume too high for EF tracking | | Soft delete by flag | ExecuteUpdate | Single statement sets IsDeleted = true |

Chunking Pattern for Background Workers

public async Task ProcessInChunksAsync(
    IReadOnlyList<Guid> ids,
    int chunkSize,
    CancellationToken ct)
{
    foreach (var chunk in ids.Chunk(chunkSize))
    {
        await using var scope = _scopeFactory.CreateAsyncScope();
        var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();

        var items = await dbContext.Orders
            .Where(o => chunk.Contains(o.Id))
            .ToListAsync(ct);

        foreach (var item in items)
        {
            item.Process();
        }

        await dbContext.SaveChangesAsync(ct);
    }
}

When to Use

• Data migrations updating thousands of rows
• Scheduled cleanup jobs (archive, purge, soft-delete)
• Bulk imports from CSV, API, or external systems
• Background workers processing large queues

When NOT to Use

• Single entity CRUD operations (standard SaveChangesAsync is fine)
• Operations requiring domain event dispatch (bulk operations bypass change tracker)
• Operations needing per-entity validation (validate before bulk, not during)

Gotchas

1. ExecuteUpdate/ExecuteDelete bypass change tracker: No SaveChanges, no interceptors, no domain events. If you need side effects, load and process entities individually.
2. Clear the tracker: When inserting in batches, call ChangeTracker.Clear() after each batch to prevent memory growth and performance degradation.
3. Transaction scope: ExecuteUpdate/ExecuteDelete runs in a single SQL statement — inherently atomic. Chunked operations need an explicit transaction if all-or-nothing is required.
4. Concurrency tokens: ExecuteUpdate does not check concurrency tokens. If optimistic concurrency is needed, load entities first.
5. Index impact: Bulk inserts can cause index fragmentation. Consider disabling non-clustered indexes during massive imports and rebuilding after.

Related Skills

• Background Hosted Service (bulk ops in workers)
• EF Core Modeling (entity configuration for bulk-friendly schemas)
• Outbox Pattern (events for bulk-processed entities)
• Index Design Strategy (index impact during bulk load operations)
• SQL Coding Standards (set-based patterns for bulk SQL operations)