EfCore.BulkOperations 2.0.0

EfCore.BulkOperations

BulkInsertAsync, BulkUpdateAsync, BulkDeleteAsync and BulkMergeAsync on your DbContext. Each one builds a single parameterised statement from EF Core's own model metadata and sends it straight to the connection, instead of going through the change tracker.

MySQL and MariaDB only. Identifiers are backtick-quoted in every statement, update and delete use MySQL's multi-table INNER JOIN form, and BulkMergeAsync is built on ON DUPLICATE KEY UPDATE. None of the four is portable to SQL Server or PostgreSQL.

Source and full benchmark · NuGet

Example

Every call takes a list and returns the number of rows affected. The examples below share one list:

var products = new List<Product> { new("Product1", 100m) };

Bulk Insert

var rowsAffected = await dbContext.BulkInsertAsync(products);

await dbContext.BulkInsertAsync(products, option =>
{
    option.BatchSize = 1000;
    option.CommandTimeout = 120;
    option.IgnoreOnInsert = x => new { x.CreatedAt };
});

Bulk Update

await dbContext.BulkUpdateAsync(products);

// Leave a column as the database has it
await dbContext.BulkUpdateAsync(products, option => option.IgnoreOnUpdate = x => new { x.CreatedAt });

// Match rows on something other than the unique index found in the model
await dbContext.BulkUpdateAsync(products, option => option.UniqueKeys = x => new { x.Id });

Bulk Delete

await dbContext.BulkDeleteAsync(products);

await dbContext.BulkDeleteAsync(products, option => option.UniqueKeys = x => new { x.Id });

Bulk Merge

Insert rows that are new and update the ones that are not, in one statement.

await dbContext.BulkMergeAsync(products);

The number returned follows MySQL's rule for ON DUPLICATE KEY UPDATE: 1 for a row that was inserted and 2 for one that was updated, so it is not the number of rows in the list. A row that matched but already had these values counts 1 with MySqlConnector's default UseAffectedRows=false (the connection reports matched rows), and 0 if the connection string sets UseAffectedRows=true.

await dbContext.BulkMergeAsync(products, option =>
{
    option.IgnoreOnInsert = x => new { x.CreatedAt };
    option.IgnoreOnUpdate = x => new { x.CreatedAt };
});

Options

Sharing one transaction

Each call runs in its own transaction unless you hand it one. A transaction you pass in stays yours: the library will not commit it, roll it back, or close the connection. The same applies to a transaction the context already has open through Database.BeginTransactionAsync() - it is picked up automatically - and to a connection you opened yourself, which is left open.

Entities with a shadow property (a foreign key EF Core added for a navigation, a TPH discriminator, or one declared with Property<T>("Name")) are rejected with NotSupportedException: there is no CLR property to read the value from.

var transaction = await dbContext.BeginTransactionAsync();

try
{
    await dbContext.Products.AddAsync(product);
    await dbContext.SaveChangesAsync();

    await dbContext.BulkInsertAsync(orders, null, transaction);
    await dbContext.BulkInsertAsync(logs, null, transaction);

    await dbContext.CommitAsync();
}
catch
{
    await dbContext.RollbackAsync();
    throw;
}

Benchmark

Against plain EF Core on the same data, schema and MySQL instance, at MySQL's default settings. Ratio is this library's mean divided by EF Core's, so 0.26 means it took 26% of the time.

Allocation is lower everywhere too, most of all on delete, which sends only the key columns: 86 MB against EF Core's 576 MB at a hundred thousand rows.

Merge is the narrowest margin because EF Core's baseline for it is already the fastest thing EF Core can do - look the rows up in chunks, then update or add each one.

Method, hardware, error bars and the batch-size sweep