Bulk Update

Required Nuget Package:

Allows bulk-update of entities.

• Enable bulk-update support
• Usage
• Bulk Update Options
  • Update subset of properties only
  • Table override
  • MERGE table hints (SQL Server)
  • Typical SqlBulkCopy options (SQL Server)
• Bulk Update from Query
  • Usage
  • Constants, variables, and expressions
  • Complex types
  • Composite key join
  • Filtering rows
  • Table override (query-based)
  • Generated SQL by provider
• 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 override

By default, the target table name and schema are resolved from the EF Core model. TableNameProvider overrides them per operation. The provider is called for the root entity type and for every owned entity type that is mapped to a separate table and takes part in the operation, nested owned types included.

Use IBulkOperationTableNameProvider.ForRootEntity(tableName, schema) when the entity has no owned types in separate tables:

// SQL Server
var options = new SqlServerBulkUpdateOptions
{
   TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers", "archive")
};

// PostgreSQL
var options = new NpgsqlBulkUpdateOptions
{
   TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers", "archive")
};

// SQLite
var options = new SqliteBulkUpdateOptions
{
   TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers")
};

await ctx.BulkUpdateAsync(customersToUpdate, options);

schema == null means that no schema is emitted, so the database uses its default schema. It does not mean the schema of the EF Core model. IBulkOperationTableNameProvider.Default resolves the table name and the schema from the EF Core model. TableNameProvider defaults to this provider.

ForRootEntityWithDefaultSchema(tableName) uses the given table name and keeps the schema of the EF Core model (ToTable(name, schema) or HasDefaultSchema). ForRootEntityWithDefaultName(schema) keeps the table name of the EF Core model and uses the given schema. A null schema means that no schema is emitted. Both factories throw for owned types in separate tables in the same way as ForRootEntity. Both factories throw for temp table entities (ConfigureTempTableEntity), because they have no regular table: a temp table entity has no model table name for ForRootEntityWithDefaultName and no defined model schema for ForRootEntityWithDefaultSchema. Use ForRootEntity(tableName, schema) for them.

// given table name, schema of the model
TableNameProvider = IBulkOperationTableNameProvider.ForRootEntityWithDefaultSchema("ArchiveCustomers")

// table name of the model, given schema
TableNameProvider = IBulkOperationTableNameProvider.ForRootEntityWithDefaultName("archive")

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

Owned types in separate tables

SQLite bulk update writes owned types that are mapped to their own table (OwnsOne(...).ToTable(...), OwnsMany(...)) to separate tables. ForRootEntity throws an InvalidOperationException when the operation reaches such an owned type. Rows of the root table may already be written at that point. Use IBulkOperationTableNameProvider.Create or implement IBulkOperationTableNameProvider to redirect the tables of the entity and of its owned types.

Create builds a provider from a delegate. The delegate is called for the root entity type and for every owned entity type that is mapped to a separate table. This example appends _Archive to the table name of the model:

var options = new SqliteBulkUpdateOptions { TableNameProvider = IBulkOperationTableNameProvider.Create(t => new BulkOperationTableName(IBulkOperationTableNameProvider.Default.GetTableName(t).Name + "_Archive")) };
await ctx.BulkUpdateAsync(customersToUpdate, options);

The example emits no schema. Pass t.GetSchema() as the second argument of BulkOperationTableName to keep the schema of the model.

Implement the interface when the rule needs more than one expression:

public sealed class ArchiveTableNameProvider : IBulkOperationTableNameProvider
{
   public BulkOperationTableName GetTableName(IEntityType entityType)
   {
      // The root entity type has no ownership. Owned entity types are distinct per navigation,
      // even when two navigations use the same CLR type, so distinguish them by the owning navigation
      // (or by entityType.Name), never by entityType.ClrType.
      var ownership = entityType.FindOwnership();
      var tableName = ownership is null ? "ArchiveCustomers" : "ArchiveCustomers_" + ownership.PrincipalToDependent!.Name;

      // Schema == null means "no schema" (database default). Use entityType.GetSchema() to keep the schema of the model.
      return new BulkOperationTableName(tableName);
   }
}

var options = new SqliteBulkUpdateOptions { TableNameProvider = new ArchiveTableNameProvider() };
await ctx.BulkUpdateAsync(customersToUpdate, options);

• The options TableName and Schema are obsolete. They override the table of the root entity only, and a part that is null keeps the value of the EF Core model. They throw when the operation reaches an owned type in a separate table. Setting TableName or Schema replaces TableNameProvider, and setting TableNameProvider clears TableName and Schema. The last assignment wins. Replace TableName = "X" by ForRootEntityWithDefaultSchema("X"), Schema = "s" by ForRootEntityWithDefaultName("s"), and both options together by ForRootEntity("X", "s"). These replacements keep the behavior of the options, with one exception: TableName alone on a temp table entity uses the schema that the EF Core model returns for it (entityType.GetSchema()), but ForRootEntityWithDefaultSchema throws for it. Use ForRootEntity(tableName, schema) for temp table entities. ForRootEntity("X") drops the schema of the EF Core model.
• The library does not wrap the writes of the root table and of the owned tables in a transaction. If the operation fails at an owned table after the root rows were written, the root rows stay in the table unless the caller started a transaction (ctx.Database.BeginTransactionAsync()). SQLite starts a transaction for performance when none is active, but callers should not rely on it for atomicity.
• SQL Server and PostgreSQL bulk update do not support owned types, see Owned Entity Types. Therefore TableNameProvider is called for the root entity type only.

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):

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, static members (e.g. smart-enum values like Status.Published), and arithmetic expressions in addition to source/target property access. Every subtree of the value selector that does not depend on the lambda parameters is evaluated once on the client and rendered as a SQL literal with the type mapping of the target column. This includes a null-guarded conditional such as captured == null ? 0 : captured.Count and a method call on a captured value such as captured.Name.Trim(). Other expressions are translated to SQL by EF Core. Therefore the only SQL parameters of the UPDATE statement come from the source query. Values of types with a value converter on the target property (e.g. smart enums) are converted using that converter.

// 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, static members, arithmetic operators (+, -, *, /, %), and any expression EF Core can translate to SQL — including server-translatable method calls such as f.Name.ToUpper() or f.Name.Substring(0, 3). Only client-only expressions that EF Core cannot translate to SQL are not supported. Static members are evaluated once at translation time, except members EF Core translates server-side (e.g. DateTime.Now).

A subtree is evaluated on the client only when it contains no lambda parameter and when EF Core translates none of its members server-side (DateTime.Now stays server-side). The subtree is evaluated as a whole, so C# semantics hold: nullableInt.HasValue ? 1 : 2 with a null nullableInt writes 2, and captured == null ? 0 : captured.Count with a null captured writes 0. A member access on a captured null throws an ArgumentException ("The member '…' of '…' cannot be read because the captured value is null. ..."), in a value selector and in the filter. Any other exception during the client evaluation throws an InvalidOperationException that names the expression and keeps the original exception as inner exception. When the test of a conditional contains no lambda parameter, the test is evaluated on the client and only the selected branch is used. The operators && and || with such a left operand are evaluated in the same way. Therefore a null guard also protects an expression that reads a column: the filter (e, f) => captured == null || e.Name == captured.Name updates all rows for a null captured, and (e, f) => captured == null ? f.Count : captured.Count + f.Count writes the value of f.Count. EF.Functions methods are always translated to SQL. A captured query (IQueryable) is never executed during the translation, so a value selector such as (e, f) => dbContext.Set<Product>().Count() throws an InvalidOperationException.

Complex types

Since version 10.6.0, the target selector of Set can reach a member of a complex type that is mapped with ComplexProperty. The complex type can be a class or a struct, and complex types can be nested.

public readonly record struct TimestampPair(long ReceivedAt, long MessageTimestamp);

public class Message
{
   public Guid Id { get; set; }
   public long MessageTimestamp { get; set; }
   public TimestampPair EffectiveMessage { get; set; }
   public string? Name { get; set; }
}

modelBuilder.Entity<Message>(builder =>
{
   builder.HasKey(e => new { e.Id, e.MessageTimestamp });
   builder.ComplexProperty(e => e.EffectiveMessage, c =>
   {
      c.Property(p => p.ReceivedAt).HasColumnName("effective_received_at");
      c.Property(p => p.MessageTimestamp).HasColumnName("effective_message_timestamp");
   });
});

Set single members of the complex property:

// PostgreSQL
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(
   updatedMessages, new NpgsqlTempTableBulkInsertOptions());

var affectedRows = await ctx.Set<Message>().BulkUpdateAsync(
   tempTable.Query,
   e => new { e.Id, e.MessageTimestamp },
   f => new { f.Id, f.MessageTimestamp },
   builder => builder
      .Set(e => e.EffectiveMessage.ReceivedAt, (e, f) => f.EffectiveMessage.ReceivedAt)
      .Set(e => e.EffectiveMessage.MessageTimestamp, (e, f) => f.EffectiveMessage.MessageTimestamp));

Set the whole complex property. The library expands this assignment into one SET assignment per member column, here effective_received_at and effective_message_timestamp. Nested complex types are expanded recursively. The value can be the complex property of the source, a captured variable, or a new expression without a lambda parameter, such as new TimestampPair(7, 8), which is evaluated once on the client:

// value from the source
builder => builder
   .Set(e => e.EffectiveMessage, (e, f) => f.EffectiveMessage)
   .Set(e => e.Name, (e, f) => f.Name)

// value from a captured variable
var effectiveMessage = new TimestampPair(7, 8);

builder => builder.Set(e => e.EffectiveMessage, (e, f) => effectiveMessage)

Members with a value converter are supported. A captured value is converted with the value converter of the target column.

A whole complex property skips the members whose value the database generates, for example computed columns (GetBeforeSaveBehavior() == PropertySaveBehavior.Ignore). The other bulk operations use the same rule. Set such a member explicitly to write it. When every writable member of a whole complex property is generated, an ArgumentException is thrown for that property, also when other columns are set ("No column can be written, because every member of the selected complex properties is generated (PropertySaveBehavior.Ignore). Expression: ...").

The following cases are not supported:

• A complex collection (a collection of complex types) throws a NotSupportedException ("The complex collection '…' is not supported.").
• A nullable complex property cannot be set as a whole and throws a NotSupportedException ("The nullable complex property '…' cannot be set as a whole."). A member of a nullable complex property cannot be set either, for a class (for example e => e.OptionalBoundary!.Upper) as well as for a struct (for example e => e.Boundary.Value.Upper). It throws a NotSupportedException ("The member '…' of '…' is a nullable complex property. ...").
• A value of a nullable type for a whole complex property, for example a captured TimestampPair? variable or a source member of type TimestampPair?, throws a NotSupportedException ("The value of the whole complex property must not be of the nullable type '…'. ..."). Use a value of the non-nullable type.
• A target selector that goes through a member that is not a complex property (for example a navigation) throws a NotSupportedException ("The member '…' is not a complex property of '…'. ...").
• A target selector that is not a member chain on the lambda parameter, for example e => e.Count + 1 or a method call, throws a NotSupportedException ("Only simple property access expressions (e => e.Property), member access through complex properties (e => e.ComplexProperty.Property) or EF.Property calls on the lambda parameter are supported. ...").
• EF.Property is supported only on the lambda parameter. EF.Property through a complex property (for example EF.Property<long>(e.EffectiveMessage, "ReceivedAt")) throws a NotSupportedException ("EF.Property is supported only on the lambda parameter, not through a complex property, a navigation or another expression. ..."). Use the member access e.EffectiveMessage.ReceivedAt instead.
• A member that is not mapped throws an InvalidOperationException ("The property '…' is not a mapped property on '…'."). For a member of a complex type, the message names the complex type.
• A captured null as the value of a whole complex property throws an ArgumentException ("The member '…' of '…' cannot be read because the captured value is null. ..."). Pass a non-null value, or set the members one by one.
• Each target column can be set only once. A second assignment to the same column throws an ArgumentException ("The target column '…' is set more than once. ..."). This rule also applies when a whole complex property and one of its members are both set. Set either the whole complex property or its members. The rule applies to all providers.

EF Core cannot bind a nested complex type to a constructor parameter. Therefore the members of a nested complex type need a setter or an init accessor.

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.

Filtering rows

Use the optional filter parameter to restrict which target rows are updated. The filter can reference both target and 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),
   filter: (e, f) => e.Score < f.Score);

Generated SQL (SQL Server):

UPDATE t SET [Score] = s.[Score]
FROM [Customers] AS t
INNER JOIN (<source>) AS s ON (t.[Id] = s.[Id])
WHERE t.[Score] < s.[Score];

Generated SQL (PostgreSQL / SQLite):

UPDATE "Customers" AS t SET "Score" = s."Score"
FROM (<source>) AS s
WHERE t."Id" = s."Id" AND t."Score" < s."Score";

The filter supports the same expression types as value selectors: property access, constants, captured variables, arithmetic, and any expression EF Core can translate to SQL — including server-translatable method calls such as f.Name.Contains("..."). Only client-only expressions that EF Core cannot translate are not supported.

Table override (query-based)

Override the target table per operation with TableNameProvider on 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
   {
      TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers", "archive")
   });

// PostgreSQL
// new NpgsqlBulkUpdateFromQueryOptions { TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers", "archive") }

// SQLite
// new SqliteBulkUpdateFromQueryOptions { TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers") }

TableNameProvider defaults to IBulkOperationTableNameProvider.Default, which resolves the table name and the schema from the EF Core model. The provider is called for the target entity type only. schema == null means that no schema is emitted, so the database uses its default schema. The options TableName and Schema are obsolete, see Table override.

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.

Owned Entity Types

Owned entity types are supported by SQLite only:

Owned type	SQL Server	PostgreSQL	SQLite
OwnsOne in the table of the owner (inlined)	-	-	Y
OwnsOne mapped to a separate table	-	-	Y
OwnsMany (always a separate table)	-	-	Y

• SQL Server and PostgreSQL throw a NotSupportedException ("Temp tables don't support owned entities.") for entities with owned types. The exception is thrown before the target table is written.
• SQLite updates the rows of owned types in separate tables by their primary key. The owned entities must be tracked by the DbContext, for example because they were loaded together with the owner, so that the values of their shadow key properties can be read.
• SQLite needs at least one column besides the key to update in the table of the root entity. If the root entity has no other column, for example an entity that consists of a key and of owned types in separate tables only, the update fails with a syntax error.