Bulk Update

Required Nuget Package:

Allows bulk-update of entities.

• Enable bulk-update support
• Usage
• Bulk Update Options
  • Update subset of properties only
  • MERGE table hints (SQL Server)
  • Typical SqlBulkCopy options (SQL Server)
• Limitations

Enable bulk-update support

Enable bulk-update support by using the extension method AddBulkOperationSupport.

If you are using Lazy Loading then disable the registration of temp tables for primites types sqlOptions.AddBulkOperationSupport(configureTempTablesForPrimitiveTypes: false).

var services = new ServiceCollection() 
                       .AddDbContext<DemoDbContext>(builder => builder
                               // SQL Server
                               .UseSqlServer("conn-string", sqlOptions =>
                                                            {
                                                                 sqlOptions.AddBulkOperationSupport();
                                                            })
                               // SQLite
                               //.UseSqlite("conn-string", sqlOptions =>
                               //                          {
                               //                                sqlOptions.AddBulkOperationSupport();
                               //                          })

Usage

Use one of the method overloads of BulkUpdateAsync to bulk-update entities. By default, the columns of the primary key are used to perform the match.

List<Customer> customersToUpdate = ...;

// update entities as a whole
await ctx.BulkUpdateAsync(customersToUpdate);

// only "FirstName" has to be sent to the DB
// alternative ways to specify the column(s):
// * c => c.FirstName
// * c => new { c.FirstName}
// * new SqlServerBulkUpdateOptions{ PropertiesToUpdate = IEntityPropertiesProvider.Include<Customer>(c => c.FirstName) }
await ctx.BulkUpdateAsync(customersToUpdate, c => new { c.Id });

// use "propertiesToMatchOn" to specify the key properties to perform the JOIN on
await ctx.BulkUpdateAsync(new[] { customer },
                          propertiesToUpdate: c => c.FirstName,
                          propertiesToMatchOn: c => c.Id);

Bulk Update Options

Use the corresponding implementation of IBulkUpdateOptions to configure the update of entities.

• SQL Server: SqlServerBulkUpdateOptions
• SQLite: SqliteBulkUpdateOptions

Update subset of properties only

By default, all properties of an entity are going to be updated. Use the options to specify the columns to update and the columns to match on.

var options = new SqlServerBulkUpdateOptions
{                 
   KeyProperties = IEntityPropertiesProvider.Include<Customer>(c => c.Id),
   PropertiesToUpdate = IEntityPropertiesProvider.Include<Customer>(c => c.FirstName)
   // use "IEntityPropertiesProvider.Exclude" to exclude properties
};

await ctx.BulkUpdateAsync(customersToInsert, options);

MERGE table hints (SQL Server)

The bulk update is implemented via MERGE command. The table hints can be configured via MergeTableHints.

var options = new SqlServerBulkUpdateOptions
{
  MergeTableHints = { SqlServerTableHintLimited.HoldLock, SqlServerTableHintLimited.RowLock }
};

Typical SqlBulkCopy options (SQL Server)

The bulk update is implemented via MERGE command. The entities are inserted into a temp table before MERGE. The creation of the temp table and the bulk insert of entities into temp table can be controlled via TempTableOptions.

var options = new SqlServerBulkUpdateOptions
{
  TempTableOptions =
  {
     BatchSize = 5_000,
     EnableStreaming = true,
     BulkCopyTimeout = TimeSpan.FromSeconds(5),
     SqlBulkCopyOptions = SqlBulkCopyOptions.Default
  }
};

Limitations

Default values

Dependeding on the database you may hit some limitations when using default values. The limitations are applied to both the default values defined using HasDefaultValueSql and HasDefaultValue.

modelBuilder.Entity<Customer>(builder =>
{
    builder.Property(e => e.StringProperyWithSqlDefaultValue).HasDefaultValueSql("'foo'");
    builder.Property(e => e.StringPropertyWithDefaultValue).HasDefaultValue("bar");
});

The Entity Framework Core is able to handle default values properly because every entity is handled individually. Generating individual SQL statements during bulk update would contradict the whole idea of this feature.

Shadow Properties

If an entity has shadow properties then the entity must be attached to the corresponding DbContext to be able to access the properties.

Owned Entity Types

Owned entity types are not supported.