Bulk Insert · PawelGerr/Thinktecture.EntityFrameworkCore Wiki · GitHub
Skip to content

Bulk Insert

Jump to bottom
Pawel Gerr edited this page Oct 12, 2026 · 13 revisions

Required Nuget Package:
Thinktecture.EntityFrameworkCore.SqlServer
Thinktecture.EntityFrameworkCore.PostgreSQL
Thinktecture.EntityFrameworkCore.Sqlite

Allows bulk-insert of entities.

Enable bulk-insert support

Enable bulk-insert 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 BulkInsertAsync to bulk-insert entities. The method returns the number of inserted rows.

List<Customer> customersToInsert = ...;

// insert entities as a whole
int numberOfInsertedRows = await ctx.BulkInsertAsync(customersToInsert);

// only "Id" has to be sent to the DB
// alternative ways to specify the column(s):
// * c => new { c.Id }
// * c => c.Id
// * new SqlServerBulkInsertOptions { PropertiesToInsert = IEntityPropertiesProvider.Exclude<Customer>(c => new { c.Id })};
numberOfInsertedRows = await ctx.BulkInsertAsync(customersToInsert, c => new { c.Id });

Bulk Insert Options

Use the corresponding implementation of IBulkInsertOptions to configure the insert of the entities.

  • SQL Server: SqlServerBulkInsertOptions
  • PostgreSQL: NpgsqlBulkInsertOptions
  • SQLite: SqliteBulkInsertOptions

Insert subset of properties only

By default, all properties of an entity are going to be inserted. You can use the options to specify the columns to insert.

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

await ctx.BulkInsertAsync(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 SqlServerBulkInsertOptions
{
   TableNameProvider = IBulkOperationTableNameProvider.ForRootEntity("ArchiveCustomers", "archive")
};

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

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

await ctx.BulkInsertAsync(customersToInsert, 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.

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

Owned types in separate tables

Owned types that are mapped to their own table (OwnsOne(...).ToTable(...), OwnsMany(...)) are written 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 SqlServerBulkInsertOptions { TableNameProvider = IBulkOperationTableNameProvider.Create(t => new BulkOperationTableName(IBulkOperationTableNameProvider.Default.GetTableName(t).Name + "_Archive")) };
await ctx.BulkInsertAsync(customersToInsert, 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, "archive");
   }
}

var options = new SqlServerBulkInsertOptions { TableNameProvider = new ArchiveTableNameProvider() };
await ctx.BulkInsertAsync(customersToInsert, 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. When you replace TableName = "X" by ForRootEntity("X"), pass the schema explicitly if the EF Core model uses a schema that is not the default schema of the database.
  • 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.

Typical SqlBulkCopy options (SQL Server)

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

PostgreSQL-specific options

PostgreSQL uses the COPY command internally for maximum performance.

var options = new NpgsqlBulkInsertOptions
{
   // FREEZE option: rows are frozen immediately, bypassing MVCC visibility checks.
   // Significantly improves performance for bulk loads into newly created/truncated tables.
   Freeze = true,

   CommandTimeout = TimeSpan.FromSeconds(60),

   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => new { c.Id, c.FirstName, c.LastName })
};

await ctx.BulkInsertAsync(customersToInsert, 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 insert would contradict the whole idea of this feature.

Possible solutions:

  • Provide all values explicitly (i.e. don't depend on the database or EF to set default values)
  • Don't insert entities as a whole, skip the properties with default values

MS SQL Server

It is not possible to trigger the default value constraint of the SQL Server if the column is NOT NULL

  • If the corresponding .NET-Property is a reference type (like a string) then SqlBulkCopy will throw a InvalidOperationException with a message Column 'MyStringColumn' does not allow DBNull.Value.
  • If the corresponding .NET-Property is a not-nullable struct (like an int) then the value is written to the database as-is, i.e. if the .NET-value is 0 then 0 is written into database. The same is true for a Guid, i.e. an empty Guid stays 00000000-0000-0000-0000-000000000000.

If the column allows NULL then .NET-Value null will trigger the default value constraint, i.e. we get the expected result.

SQLite

With SQLite the default value constraint doesn't trigger when trying to send null / NULL / DBNull.Value to both NULL and NOT NULL columns. The only way to trigger the constraint is not to insert the corresponding property alltogether.

Bulk Insert from Query

Use BulkInsertAsync with an IQueryable<TSource> to insert rows from a server-side query without materializing data client-side. This generates INSERT INTO ... SELECT ... FROM (subquery).

Usage

// Insert from a temp table
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(sourceEntities, new SqlServerTempTableBulkInsertOptions());

var affectedRows = await ctx.Set<Customer>().BulkInsertAsync(
    tempTable.Query,
    builder => builder
        .Map(e => e.Id, f => f.Id)
        .Map(e => e.Name, f => f.Name)
        .Map(e => e.Email, f => f.Email));

// Insert from a DbSet query
var sourceQuery = ctx.Set<Customer>()
                     .Where(c => c.IsActive)
                     .Select(c => new { c.Id, c.Name, c.Email });

var affectedRows = await ctx.Set<ArchivedCustomer>().BulkInsertAsync(
    sourceQuery,
    builder => builder
        .Map(e => e.Id, f => f.Id)
        .Map(e => e.Name, f => f.Name)
        .Map(e => e.Email, f => f.Email));

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 property access. Constant values are emitted as auto-generated SQL parameters (named __insert_const_0, __insert_const_1, etc.); more complex expressions are translated to SQL by EF Core. Values of types with a value converter on the target property (e.g. smart enums) are converted using that converter.

// Map a column to a constant value
var affectedRows = await ctx.Set<Customer>().BulkInsertAsync(
    sourceQuery,
    builder => builder
        .Map(e => e.Id, f => f.Id)
        .Map(e => e.Name, f => f.Name)
        .Map(e => e.Score, f => 42));             // constant

// Map a column to a captured variable
var defaultRegion = "EU";

var affectedRows = await ctx.Set<Customer>().BulkInsertAsync(
    sourceQuery,
    builder => builder
        .Map(e => e.Id, f => f.Id)
        .Map(e => e.Name, f => f.Name)
        .Map(e => e.Region, f => defaultRegion));  // captured variable

// Arithmetic on source properties
var affectedRows = await ctx.Set<Customer>().BulkInsertAsync(
    sourceQuery,
    builder => builder
        .Map(e => e.Id, f => f.Id)
        .Map(e => e.Name, f => f.Name)
        .Map(e => e.Score, f => f.Score * 2 + 1)); // arithmetic expression

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

Table override

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

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

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

await ctx.Set<Customer>().BulkInsertAsync(sourceQuery, mapBuilder, options);

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

All three providers generate the same structure (only identifier quoting differs):

-- SQL Server
INSERT INTO [Schema].[Table] ([Col1], [Col2])
SELECT s.[SrcCol1], s.[SrcCol2]
FROM (
  <source_query>
) AS s;

-- PostgreSQL / SQLite
INSERT INTO "Schema"."Table" ("Col1", "Col2")
SELECT s."SrcCol1", s."SrcCol2"
FROM (
  <source_query>
) AS s;

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.

Performance

Features

Convenience

Best Practices

Integration Testing

Extensibility

AI Coding Assistants

Clone this wiki locally