Bulk Update

Required Nuget Package:

[EXPERIMENTAL]

Allows bulk-update of entities.

Enable bulk-update support
Usage
Bulk Update Options
Bulk Update from Query
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();
                                                            })
                               // PostgreSQL
                               //.UseNpgsql("conn-string", npgsqlOptions =>
                               //                          {
                               //                                npgsqlOptions.AddBulkOperationSupport();
                               //                          })
                               // SQLite
                               //.UseSqlite("conn-string", sqliteOptions =>
                               //                          {
                               //                                sqliteOptions.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
PostgreSQL: NpgsqlBulkUpdateOptions
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);

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 SqlServerBulkUpdateOptions
{
   TableName = "ArchiveCustomers",
   Schema = "archive"
};

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

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

await ctx.BulkUpdateAsync(customersToUpdate, options);

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

MERGE table hints (SQL Server)

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

PostgreSQL uses a temp table with UPDATE ... FROM instead of MERGE.

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

Bulk Update from Query

Required NuGet Package (one of):
[EXPERIMENTAL]

Performs a server-side UPDATE ... FROM using an IQueryable<TSource> as the data source. This avoids materializing data client-side — the source can be a temp table query, a regular DbSet query, or any composable LINQ query.

The method joins the target table to the source subquery on specified key columns and applies property assignments defined via a fluent SetPropertyBuilder.

Usage

The API is the same across all three providers — only the options class differs:

// Insert updated values into a temp table
var updatedCustomers = new List<Customer>
{
   new() { Id = customerId1, FirstName = "Updated1", Score = 10 },
   new() { Id = customerId2, FirstName = "Updated2", Score = 20 }
};

// SQL Server
await using var tempTable = await executor.BulkInsertIntoTempTableAsync(
   updatedCustomers, new SqlServerTempTableBulkInsertOptions());

// PostgreSQL
// await using var tempTable = await executor.BulkInsertIntoTempTableAsync(
//    updatedCustomers, new NpgsqlTempTableBulkInsertOptions());

// SQLite
// await using var tempTable = await executor.BulkInsertIntoTempTableAsync(
//    updatedCustomers, new SqliteTempTableBulkInsertOptions());

// Update the target table from the temp table query
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   tempTable.Query,                               // IQueryable<Customer> source
   target => target.Id,                            // target join key
   source => source.Id,                            // source join key
   builder => builder
      .Set(e => e.FirstName, (e, f) => f.FirstName)  // assign from source
      .Set(e => e.Score, (e, f) => f.Score));

The source can also be a regular DbSet query:

var sourceQuery = ctx.Set<Customer>().Where(c => c.IsActive);

var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder.Set(e => e.Score, (e, f) => f.Score));

Constants, variables, and expressions

Value selectors support constants, captured variables, and arithmetic expressions in addition to source/target property access. Constant values are emitted as SQL parameters (@__bv_0, @__bv_1, etc.); expressions are translated to SQL with parameters (@__ev_0, @__ev_1, etc.).

// Set a column to a constant value
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Score, (e, f) => 42)              // constant
      .Set(e => e.FirstName, (e, f) => f.FirstName)); // source property

// Set a column to a captured variable
var newStatus = "Archived";

var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Status, (e, f) => newStatus)       // captured variable
      .Set(e => e.Score, (e, f) => f.Score));          // source property

// Arithmetic on source properties
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Score, (e, f) => f.Score + 10));    // source + constant

// Mixed target and source arithmetic
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Score, (e, f) => e.Score + f.Score));  // target + source

// Complex arithmetic
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Score, (e, f) => e.Score + f.Score * 2)); // target + source * 2

Note: Value selectors support property access, EF.Property calls, constants, captured variables, and arithmetic operators (+, -, *, /, %). Method calls on properties (e.g., f.Name.Length, f.Name.ToUpper()) are not supported.

Composite key join

Use anonymous types to join on multiple columns:

var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => new { e.Id, e.Region },
   f => new { f.Id, f.Region },
   builder => builder.Set(e => e.Score, (e, f) => f.Score));

Keys are matched by position (first target key ↔ first source key). The number of target and source keys must be the same.

Table/schema override

Override the target table name or schema per-operation using the provider-specific options class:

// SQL Server
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder.Set(e => e.Score, (e, f) => f.Score),
   new SqlServerBulkUpdateFromQueryOptions
   {
      TableName = "ArchiveCustomers",
      Schema = "archive"
   });

// PostgreSQL
// new NpgsqlBulkUpdateFromQueryOptions { TableName = "ArchiveCustomers", Schema = "archive" }

// SQLite
// new SqliteBulkUpdateFromQueryOptions { TableName = "ArchiveCustomers" }

When null (the default), the table name and schema are resolved from the EF Core model metadata.

Generated SQL by provider

SQL Server — uses UPDATE ... FROM ... INNER JOIN:

UPDATE t
SET
   [FirstName] = s.[FirstName], [Score] = s.[Score]
FROM [Schema].[Customers] AS t
INNER JOIN (
  SELECT [p].[Id], [p].[FirstName], [p].[Score]
  FROM [#TempTable_1] AS [p]
) AS s ON (t.[Id] = s.[Id])
;

PostgreSQL / SQLite — uses UPDATE ... FROM ... WHERE:

UPDATE "Schema"."Customers" AS t
SET
   "FirstName" = s."FirstName", "Score" = s."Score"
FROM (
  SELECT "p"."Id", "p"."FirstName", "p"."Score"
  FROM "#TempTable_1" AS "p"
) AS s
WHERE t."Id" = s."Id"
;

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.

Bulk Update

Enable bulk-update support

Usage

Bulk Update Options

Update subset of properties only

Table and schema override

MERGE table hints (SQL Server)

Typical SqlBulkCopy options (SQL Server)

Bulk Update from Query

Usage

Constants, variables, and expressions

Composite key join

Table/schema override

Generated SQL by provider

Limitations

Default values

Shadow Properties

Owned Entity Types

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Performance

Features

Convenience

Integration Testing

Extensibility

Clone this wiki locally