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
  • Table and schema override
  • Insert-only
  • MERGE table hints (SQL Server)
  • Typical SqlBulkCopy options (SQL Server)
  • PostgreSQL: Conflict Do Nothing
• 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();
                                                            })
                               // PostgreSQL
                               //.UseNpgsql("conn-string", npgsqlOptions =>
                               //                          {
                               //                                npgsqlOptions.AddBulkOperationSupport();
                               //                          })
                               // SQLite
                               //.UseSqlite("conn-string", sqliteOptions =>
                               //                          {
                               //                                sqliteOptions.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
• PostgreSQL: NpgsqlBulkInsertOrUpdateOptions
• 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);

Table and schema override

By default, the target table name and schema are resolved from the EF Core model metadata. You can override them per-operation via options. When null (the default), the model-based resolution is used.

// SQL Server
var options = new SqlServerBulkInsertOrUpdateOptions
{
   TableName = "ArchiveCustomers",
   Schema = "archive"
};

// PostgreSQL
var options = new NpgsqlBulkInsertOrUpdateOptions
{
   TableName = "ArchiveCustomers",
   Schema = "archive"
};

// SQLite
var options = new SqliteBulkInsertOrUpdateOptions
{
   TableName = "ArchiveCustomers"
};

await ctx.BulkInsertOrUpdateAsync(customersToUpsert, options);

Note: Column resolution still uses the EF Core model. The target table must have compatible columns.

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 on SQL Server. The table hints can be configured via MergeTableHints.

PostgreSQL uses INSERT ... ON CONFLICT instead of MERGE.

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
  }
};

PostgreSQL: Conflict Do Nothing

PostgreSQL uses INSERT ... ON CONFLICT for upserts. You can set ConflictDoNothing to silently skip conflicting rows instead of updating them.

var options = new NpgsqlBulkInsertOrUpdateOptions
{
  // Uses ON CONFLICT ... DO NOTHING instead of DO UPDATE SET.
  // Conflicting rows are silently skipped rather than updated.
  ConflictDoNothing = true
};

await ctx.BulkInsertOrUpdateAsync(customersToUpsert, options);

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.