Bulk Insert

Required Nuget Package:

Allows bulk-insert of entities.

• Enable bulk-insert support
• Usage
• Bulk Insert Options
  • Insert subset of properties only
  • Typical SqlBulkCopy options (SQL Server)
• Limitations

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();
                                                            })
                               // SQLite
                               //.UseSqlite("conn-string", sqlOptions =>
                               //                          {
                               //                                sqlOptions.AddBulkOperationSupport();
                               //                          })

Usage

Use one of the method overloads of BulkInsertAsync to bulk-insert entities.

List<Customer> customersToInsert = ...;

// insert entities as a whole
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 })};
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
• 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);

Typical SqlBulkCopy options (SQL Server)

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

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.

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.