Bulk Upsert (Insert or Update)

Required Nuget Package:

Allows bulk-upsert of entities.

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

Enable bulk-upsert support

Enable bulk-upsert 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 BulkInsertOrUpdateAsync to bulk-upsert entities.

List<Customer> customersToUpsert = ...;

// update entities as a whole
await ctx.BulkInsertOrUpdateAsync(customersToUpsert);

await ctx.BulkInsertOrUpdateAsync(customersToUpsert, propertiesToUpdate: c => new { c.Id });

await ctx.BulkInsertOrUpdateAsync(new[] { newCustomer, customer },
                                  propertiesToInsert: c => new { c.Id, c.FirstName },
                                  propertiesToUpdate: c => c.FirstName,
                                  propertiesToMatchOn: c => c.Id);

Bulk Upsert Options

Use the corresponding implementation of IBulkInsertOrUpdateOptions to configure the upsert of entities.

• SQL Server: SqlServerBulkInsertOrUpdateOptions
• SQLite: SqliteBulkInsertOrUpdateOptions

Upsert subset of properties only

By default, all properties of an entity are going to be upserted. You can use the options to specify the columns to insert, to update and to match on.

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

await ctx.BulkInsertOrUpdateAsync(customersToUpsert, options);

Insert-only

By providing 0 PropertiesToUpdate the UPDATE part is skipped completely.

var options = new SqlServerBulkInsertOrUpdateOptions
{
  PropertiesToUpdate = IEntityPropertiesProvider.Empty
};

await ctx.BulkInsertOrUpdateAsync(customersToUpsert, options);

MERGE table hints (SQL Server)

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

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

Typical SqlBulkCopy options (SQL Server)

The bulk upsert 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 SqlServerBulkInsertOrUpdateOptions
{
  TempTableOptions =
  {
     BatchSize = 5_000,
     EnableStreaming = true,
     BulkCopyTimeout = TimeSpan.FromSeconds(5),
     SqlBulkCopyOptions = SqlBulkCopyOptions.Default
  }
};

Limitations

Default values

Depending 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.