Bulk Inserts & Updates

Use Case: Bulk Inserts & Updates (Writing Millions of Rows)

The everyday problem: You need to import a 2-million-row CSV, sync a product catalogue from a supplier feed, archive last year's orders, or apply a price increase to every product in a category. The naive approach — loop, _db.Add(entity), then one SaveChanges() — generates one INSERT statement per row, runs them in a single tracked transaction, and takes minutes (or times out) for what should take seconds. The challenge is writing large volumes to the database efficiently, without exhausting memory, blowing the transaction log, or locking the table for everyone else.

Six approaches, roughly ordered from "EF Core convenience" to "raw database power". The right one depends on whether you are inserting, updating, or upserting, and how much you value EF Core integration versus raw speed.

---

Way 1: Batched SaveChanges with AutoDetectChanges Off

Best used for: Moderate inserts (a few thousand to tens of thousands of rows) where you want to stay inside EF Core and keep entity behaviour (navigation fixup, value converters, generated keys).

The concept: EF Core 7+ already batches multiple INSERT statements into a single round-trip (up to a provider limit). The performance killer at scale is the change tracker calling DetectChanges() on every Add() — an O(n²) cost as the tracked set grows. Turn it off during the bulk load, add in chunks, and save per chunk.

// Batched SaveChanges — staying in EF Core, but tuned for bulk
public async Task ImportProductsAsync(IAsyncEnumerable<Product> products, CancellationToken ct)
{
    const int batchSize = 1000;

    // Disable automatic change detection — the single biggest EF bulk win
    _db.ChangeTracker.AutoDetectChangesEnabled = false;

    try
    {
        var batch = new List<Product>(batchSize);

        await foreach (var product in products.WithCancellation(ct))
        {
            batch.Add(product);
            _db.Products.Add(product);

            if (batch.Count >= batchSize)
            {
                await _db.SaveChangesAsync(ct);   // one batched round-trip
                _db.ChangeTracker.Clear();        // release tracked entities — frees memory
                batch.Clear();
            }
        }

        if (batch.Count > 0)
            await _db.SaveChangesAsync(ct);        // final partial batch
    }
    finally
    {
        _db.ChangeTracker.AutoDetectChangesEnabled = true;
    }
}

Why it works: Clearing the change tracker after each batch keeps memory flat and stops DetectChanges() from scanning an ever-growing graph. EF Core 7+ bundles the inserts in each SaveChanges into batched SQL automatically, so 1,000 rows is roughly one round-trip, not 1,000.

Benchmark note: Inserting 100,000 rows with naive per-row SaveChanges() can take 2–5 minutes. The same with AutoDetectChangesEnabled = false, batching at 1,000, and clearing the tracker drops to ~10–20 seconds — roughly a 10–15× improvement with zero new dependencies. It is still 5–20× slower than true bulk copy (Way 4) because each row is a parameterised value in an INSERT statement, but it preserves EF Core semantics. This is the right first step before reaching for heavier tools.

---

Way 2: ExecuteUpdate & ExecuteDelete (EF Core 7+)

Best used for: Set-based updates and deletes — "mark all orders older than a year as archived", "increase every electronics price by 5%", "delete all soft-deleted rows" — where you change many rows by a single rule, not row-by-row.

The concept: Before EF Core 7, updating many rows meant loading them all into memory, mutating each, and saving — pulling a million rows to the app just to change one column. ExecuteUpdate and ExecuteDelete translate a LINQ query directly into a single UPDATE ... WHERE or DELETE ... WHERE statement that runs entirely in the database. Nothing is loaded into the application.

// ExecuteUpdate — one SQL UPDATE, no entities loaded
public async Task<int> ApplyPriceIncreaseAsync(string category, decimal percent, CancellationToken ct)
{
    int rowsAffected = await _db.Products
        .Where(p => p.Category == category && p.IsActive)
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(p => p.Price, p => p.Price * (1 + percent / 100))
            .SetProperty(p => p.UpdatedAt, _ => DateTime.UtcNow), ct);

    return rowsAffected;
    // SQL: UPDATE Products SET Price = Price * @factor, UpdatedAt = @now
    //      WHERE Category = @category AND IsActive = 1
}

// ExecuteDelete — one SQL DELETE
public async Task<int> PurgeArchivedAsync(DateTime cutoff, CancellationToken ct)
    => await _db.Orders
        .Where(o => o.Status == "Archived" && o.ArchivedAt < cutoff)
        .ExecuteDeleteAsync(ct);
    // SQL: DELETE FROM Orders WHERE Status = 'Archived' AND ArchivedAt < @cutoff

Why it works: The work happens where the data lives. Instead of SELECT → materialise → mutate → UPDATE (four steps and a million-row memory spike), it is a single statement the database optimises and executes against its own pages.

Benchmark note: Updating 1 million rows the old way (load, modify, save) can take minutes and gigabytes of RAM. ExecuteUpdate runs in the database in seconds with near-zero application memory. Caveat: these methods bypass the change tracker and do not run SaveChanges interceptors, soft-delete query filters on the write, or DbContext events — they are raw set operations. If you have audit logic in SaveChanges, it will not fire. For very large deletes, batch them (delete in chunks of 10,000 in a loop) to avoid lock escalation and transaction-log blowout.

---

Way 3: Table-Valued Parameters (TVP) for Upserts

Best used for: SQL Server upserts — sending a batch of rows to a stored procedure that decides per row whether to insert or update (MERGE), all in one round-trip.

The concept: A table-valued parameter lets you pass an entire DataTable as a single parameter to a stored procedure. The procedure uses MERGE to upsert the whole batch server-side — one network round-trip carries thousands of rows, and the database resolves insert-vs-update in a single set operation.

-- SQL Server: define the table type and the merge procedure
CREATE TYPE dbo.ProductTvp AS TABLE (
    Sku         NVARCHAR(50)  NOT NULL,
    Name        NVARCHAR(200) NOT NULL,
    Price       DECIMAL(18,2) NOT NULL,
    PRIMARY KEY (Sku)
);

CREATE PROCEDURE dbo.UpsertProducts @Products dbo.ProductTvp READONLY AS
BEGIN
    SET NOCOUNT ON;
    MERGE dbo.Products AS target
    USING @Products AS source ON target.Sku = source.Sku
    WHEN MATCHED THEN
        UPDATE SET Name = source.Name, Price = source.Price, UpdatedAt = GETUTCDATE()
    WHEN NOT MATCHED THEN
        INSERT (Sku, Name, Price, CreatedAt)
        VALUES (source.Sku, source.Name, source.Price, GETUTCDATE());
END

// C# — send the whole batch as one TVP
public async Task UpsertProductsAsync(IEnumerable<Product> products, CancellationToken ct)
{
    var table = new DataTable();
    table.Columns.Add("Sku",   typeof(string));
    table.Columns.Add("Name",  typeof(string));
    table.Columns.Add("Price", typeof(decimal));

    foreach (var p in products)
        table.Rows.Add(p.Sku, p.Name, p.Price);

    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync(ct);

    await using var cmd = new SqlCommand("dbo.UpsertProducts", conn)
    {
        CommandType = CommandType.StoredProcedure
    };
    var tvp = cmd.Parameters.AddWithValue("@Products", table);
    tvp.SqlDbType = SqlDbType.Structured;
    tvp.TypeName  = "dbo.ProductTvp";

    await cmd.ExecuteNonQueryAsync(ct);
    // One round-trip upserts the entire batch via MERGE
}

Why it works: One round-trip, one set-based MERGE. The database handles the insert-or-update decision for thousands of rows simultaneously using its join machinery, rather than your application issuing thousands of "does this exist?" checks.

Benchmark note: Upserting 50,000 rows with a per-row "select then insert or update" pattern can be tens of thousands of round-trips and take minutes. A single TVP + MERGE call completes in low single-digit seconds. Send in batches of a few thousand to tens of thousands per call — a TVP with millions of rows in one call uses significant server memory for the table variable. MERGE has known concurrency caveats under heavy parallel writes; for high-contention upserts, an explicit UPDATE-then-INSERT with proper isolation can be safer.

---

Way 4: SqlBulkCopy (SQL Server) — the insert speed king

Best used for: Pure high-volume inserts into SQL Server — CSV imports, data migrations, staging-table loads of hundreds of thousands to millions of rows.

The concept: SqlBulkCopy uses the same bulk-load protocol as the bcp utility — it bypasses individual INSERT statements entirely and streams rows to the server in a compact format with minimal logging (under the right recovery model). It is the fastest managed way to insert into SQL Server.

// SqlBulkCopy — streams rows via the bulk protocol
public async Task BulkInsertProductsAsync(IEnumerable<Product> products, CancellationToken ct)
{
    using var table = new DataTable();
    table.Columns.Add("Sku",   typeof(string));
    table.Columns.Add("Name",  typeof(string));
    table.Columns.Add("Price", typeof(decimal));
    foreach (var p in products)
        table.Rows.Add(p.Sku, p.Name, p.Price);

    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync(ct);

    using var bulk = new SqlBulkCopy(conn)
    {
        DestinationTableName = "dbo.Products",
        BatchSize            = 5000,     // rows per network batch
        BulkCopyTimeout      = 300,      // seconds — generous for large loads
        EnableStreaming      = true      // stream from the DataTable/reader
    };

    // Map source columns to destination — explicit mapping avoids ordinal mistakes
    bulk.ColumnMappings.Add("Sku",   "Sku");
    bulk.ColumnMappings.Add("Name",  "Name");
    bulk.ColumnMappings.Add("Price", "Price");

    await bulk.WriteToServerAsync(table, ct);
}

// Even faster: feed SqlBulkCopy an IDataReader instead of a DataTable
// to avoid buffering all rows in the DataTable first:
// await bulk.WriteToServerAsync(myDbDataReader, ct);

Why it works: The bulk-load protocol skips the per-statement parse/plan/log overhead. With EnableStreaming and an IDataReader source, rows flow from your source straight to the server without ever sitting in a full in-memory collection.

Benchmark note: SqlBulkCopy routinely inserts 1 million rows in 3–10 seconds versus several minutes for batched EF Core inserts — frequently 20–50× faster. To get the best numbers: load into a staging table with no indexes, then build indexes after; use the TableLock option (SqlBulkCopyOptions.TableLock) to enable minimal logging; and ensure the database is in SIMPLE or BULK_LOGGED recovery for the load. The trade-off is that it bypasses EF Core entirely — no entity events, no generated keys returned, no validation.

---

Way 5: PostgreSQL COPY via Npgsql Binary Import

Best used for: The PostgreSQL equivalent of SqlBulkCopy — fastest possible inserts into Postgres for imports and migrations.

The concept: PostgreSQL's COPY ... FROM STDIN command is its native bulk-load path. Npgsql exposes it through BeginBinaryImport, which streams rows in PostgreSQL's binary format — far faster than multi-row INSERT statements.

// Npgsql binary COPY — fastest bulk insert into PostgreSQL
public async Task BulkInsertProductsAsync(IEnumerable<Product> products, CancellationToken ct)
{
    await using var conn = new NpgsqlConnection(_connectionString);
    await conn.OpenAsync(ct);

    await using var writer = await conn.BeginBinaryImportAsync(
        "COPY products (sku, name, price, created_at) FROM STDIN (FORMAT BINARY)", ct);

    var now = DateTime.UtcNow;
    foreach (var p in products)
    {
        await writer.StartRowAsync(ct);
        await writer.WriteAsync(p.Sku,   NpgsqlTypes.NpgsqlDbType.Varchar, ct);
        await writer.WriteAsync(p.Name,  NpgsqlTypes.NpgsqlDbType.Varchar, ct);
        await writer.WriteAsync(p.Price, NpgsqlTypes.NpgsqlDbType.Numeric, ct);
        await writer.WriteAsync(now,     NpgsqlTypes.NpgsqlDbType.TimestampTz, ct);
    }

    // Complete commits the import; not calling it rolls everything back
    await writer.CompleteAsync(ct);
}

Why it works: COPY is PostgreSQL's purpose-built bulk channel. The binary format avoids text parsing on the server, and rows stream continuously rather than as discrete statements — no per-row planning, no statement overhead.

Benchmark note: Npgsql binary COPY can insert 1 million rows in 2–6 seconds, often 30–50× faster than row-by-row inserts and noticeably faster than even multi-row INSERT batches. As with SQL Server, drop or disable indexes and triggers during the load and rebuild after for the best throughput. CompleteAsync() is mandatory — forgetting it silently discards the entire import. For upserts, COPY into a temp/staging table, then INSERT ... ON CONFLICT DO UPDATE from staging into the target.

---

Way 6: Dedicated Bulk Libraries (EFCore.BulkExtensions)

Best used for: Teams that want near-SqlBulkCopy speed but with EF Core ergonomics — bulk insert/update/upsert/delete of entity objects (not DataTables), with cross-provider support.

The concept: Libraries like EFCore.BulkExtensions wrap the provider's native bulk facilities (SqlBulkCopy, Npgsql COPY) behind EF Core extension methods that take your entity lists directly. You keep working with Product objects; the library generates the fast path under the hood.

// EFCore.BulkExtensions — bulk operations on entities, no DataTable plumbing
using EFCore.BulkExtensions;

public async Task BulkSyncProductsAsync(List<Product> products, CancellationToken ct)
{
    // Bulk insert — uses SqlBulkCopy / COPY under the hood
    await _db.BulkInsertAsync(products, cancellationToken: ct);

    // Bulk update — set-based update from the entity list
    await _db.BulkUpdateAsync(products, cancellationToken: ct);

    // Bulk upsert (insert or update by key) in one call
    await _db.BulkInsertOrUpdateAsync(products, cancellationToken: ct);

    // Bulk delete
    await _db.BulkDeleteAsync(products, cancellationToken: ct);

    // Config knobs when needed
    await _db.BulkInsertAsync(products, new BulkConfig
    {
        BatchSize           = 5000,
        SetOutputIdentity   = true,   // populate generated keys back onto entities
        PreserveInsertOrder = true
    }, cancellationToken: ct);
}

Why it works: You get the provider's native bulk speed without hand-writing DataTable mapping or COPY writers, and the same API works across SQL Server and PostgreSQL. SetOutputIdentity even returns generated keys — something raw SqlBulkCopy does not do easily.

Benchmark note: Performance lands close to raw SqlBulkCopy (typically within 10–30%) while reading entity lists directly — a strong default for most bulk needs. The trade-offs: it is a third-party dependency to vet and keep current with EF Core releases, and licensing terms vary by library and usage (check the license for commercial use). For a one-off migration, raw SqlBulkCopy/COPY avoids the dependency; for ongoing application bulk operations, the ergonomics usually justify it.

---

Choosing the right approach

• A few thousand inserts, keep EF semantics → Batched SaveChanges, tracker off (Way 1)
• Update/delete many rows by a rule → ExecuteUpdate / ExecuteDelete (Way 2)
• Upsert batches on SQL Server → Table-valued parameter + MERGE (Way 3)
• Fastest raw insert, SQL Server → SqlBulkCopy (Way 4)
• Fastest raw insert, PostgreSQL → Npgsql binary COPY (Way 5)
• Bulk on entities with EF ergonomics → EFCore.BulkExtensions (Way 6)

Rules that apply across all six: wrap large writes in an explicit transaction only if you truly need all-or-nothing — long transactions bloat the log and hold locks; for huge loads, commit in batches instead. Drop or disable non-clustered indexes and triggers before a massive load and rebuild them afterward (rebuilding once is far cheaper than maintaining per row). And always load into a staging table first when the target is a live, queried table — then swap or merge — so you never lock the production table for the duration of the import.

Note: this is an informational engineering overview. Benchmark figures are illustrative order-of-magnitude estimates — actual numbers depend on schema, indexes, row width, hardware, recovery model, and database configuration. Always measure against your own workload before optimising.