Repository navigation
Bulk Insert
Required Nuget Package:
Allows bulk-insert of entities.
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();
// })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 });Use the corresponding implementation of IBulkInsertOptions to configure the insert of the entities.
- SQL Server:
SqlServerBulkInsertOptions - PostgreSQL:
NpgsqlBulkInsertOptions - SQLite:
SqliteBulkInsertOptions
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);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.
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 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
TableNameandSchemaare obsolete. They override the table of the root entity only, and a part that isnullkeeps the value of the EF Core model. They throw when the operation reaches an owned type in a separate table. SettingTableNameorSchemareplacesTableNameProvider, and settingTableNameProviderclearsTableNameandSchema. The last assignment wins. ReplaceTableName = "X"byForRootEntityWithDefaultSchema("X"),Schema = "s"byForRootEntityWithDefaultName("s"), and both options together byForRootEntity("X", "s"). These replacements keep the behavior of the options, with one exception:TableNamealone on a temp table entity uses the schema that the EF Core model returns for it (entityType.GetSchema()), butForRootEntityWithDefaultSchemathrows for it. UseForRootEntity(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.
var options = new SqlServerBulkInsertOptions
{
BatchSize = 5_000,
EnableStreaming = true,
BulkCopyTimeout = TimeSpan.FromSeconds(5),
SqlBulkCopyOptions = SqlBulkCopyOptions.Default
};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);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
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) thenSqlBulkCopywill throw aInvalidOperationExceptionwith a messageColumn '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 is0then0is written into database. The same is true for aGuid, i.e. an emptyGuidstays00000000-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.
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.
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).
// 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));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. A value selector that is evaluated completely on the client is bound as an auto-generated SQL parameter (named __insert_const_0, __insert_const_1, etc.), for example a constant, a captured variable, a static member, captured == null ? 0 : captured.Count or a new expression. A constant inside a larger expression is rendered as a SQL literal. Every subtree of the value selector that does not depend on the lambda parameter is evaluated once on the client, for example a method call on a captured value such as captured.Name.Trim(). Other 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 expressionNote: Value selectors support property access,
EF.Propertycalls, constants, captured variables, static members, arithmetic operators (+,-,*,/,%), and any expression EF Core can translate to SQL — including server-translatable method calls such asf.Name.ToUpper()orf.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 inserts 2, and captured == null ? 0 : captured.Count with a null captured inserts 0. A member access on a captured null throws an ArgumentException ("The member '…' of '…' cannot be read because the captured value is null. ..."). 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, so a null guard also protects a branch that reads a column: f => captured == null ? f.Count : captured.Count + f.Count with a null captured inserts the value of f.Count. The operators && and || with such a left operand are evaluated in the same way. EF.Functions methods (e.g. EF.Functions.RowNumber(EF.Functions.OrderBy(1))) are always translated to SQL. A captured query (IQueryable) is never executed during the translation, so a value selector such as f => dbContext.Set<Product>().Count() throws an InvalidOperationException.
Since version 10.6.0, the target selector of Map 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. The example uses the following entity:
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; }
}
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");
});
});Map single members, or map the whole complex property. The library expands a whole complex property into one column per member, 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.
// PostgreSQL
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(
messages, new NpgsqlTempTableBulkInsertOptions());
// single members
var affectedRows = await ctx.Set<Message>().BulkInsertAsync(
tempTable.Query,
builder => builder
.Map(e => e.Id, f => f.Id)
.Map(e => e.MessageTimestamp, f => f.MessageTimestamp)
.Map(e => e.EffectiveMessage.ReceivedAt, f => f.EffectiveMessage.ReceivedAt)
.Map(e => e.EffectiveMessage.MessageTimestamp, f => f.EffectiveMessage.MessageTimestamp));
// whole complex property
var affectedRows = await ctx.Set<Message>().BulkInsertAsync(
tempTable.Query,
builder => builder
.Map(e => e.Id, f => f.Id)
.Map(e => e.MessageTimestamp, f => f.MessageTimestamp)
.Map(e => e.EffectiveMessage, f => 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. Map 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 mapped ("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 mapped 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 mapped either, for a class (for examplee => e.OptionalBoundary!.Upper) as well as for a struct (for examplee => e.Boundary.Value.Upper). It throws aNotSupportedException("The member '…' of '…' is a nullable complex property. ..."). - 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 + 1or a method call, throws aNotSupportedException("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.Propertyis supported only on the lambda parameter.EF.Propertythrough a complex property (for exampleEF.Property<long>(e.EffectiveMessage, "ReceivedAt")) throws aNotSupportedException("EF.Property is supported only on the lambda parameter, not through a complex property, a navigation or another expression. ..."). Use the member accesse.EffectiveMessage.ReceivedAtinstead. - 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
nullas the value of a whole complex property throws anArgumentException("The member '…' of '…' cannot be read because the captured value is null. ..."). Pass a non-null value, or map the members one by one. - Each target column can be mapped only once. A second mapping to the same column throws an
ArgumentException("The target column '…' is mapped more than once. ..."). This rule also applies when a whole complex property and one of its members are both mapped. Map 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.
// 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.
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;If an entity has shadow properties then the entity must be attached to the corresponding DbContext to be able to access the properties.