Required Nuget Package:

Allows bulk insert of values and complex objects into temp tables.

• Configuration
• Usage
• Bulk Insert Options
  • Temp tables with a subset of properties
  • Primary key creation
  • Typical SqlBulkCopy options (SQL Server)
  • Reuse of the temp table within the same connection
• Recommendations
• Limitations

Configuration

Enable temp table support

Enable temp table support 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();
                               //                          })

Configure Temp Tables

Before using a temp table with EF Core, we have to introduce it to our DbContext using the extension method ConfigureTempTable with desired column type(s). The extension method returns an instance of EntityTypeBuilder<TEntity> for further configuration if necessary.

Remarks: For different column types we have to make multiple calls of ConfigureTempTable. For example one temp table with Guid, the other with Guid? (i.e. nullable Guid), the third with a string, etc. Btw, temp tables for primitive types like int, Guid, bool, decimal, string, etc. are registered automatically.

The temp-table entities are "keyless" by default, i.e. they have no primary key.

public class DemoDbContext : DbContext
{
   ...

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      ...

      /* Registrations without further configuration */

      modelBuilder.ConfigureTempTable<Guid>();                // introduces a table with 1 column of type Guid
      modelBuilder.ConfigureTempTable<Guid, Guid>();          // introduces a table with 2 columns
      modelBuilder.ConfigureTempTableEntity<MyTempTable>();   // custom type with N columns

      /* Registrations with further configuration */

      modelBuilder.ConfigureTempTable<decimal>(builder =>
                                           {
                                              builder.Property(e => e.Column1).HasPrecision(10, 5);
                                           });
      modelBuilder.ConfigureTempTableEntity<MyTempTable>(builder =>
                                           {
                                              builder.Property(e => e.MyDecimalProperty).HasPrecision(10, 5);
                                              builder.Property(e => e.MyStringProperty).HasMaxLength(200);
                                           });
   }
}

Usage

Use one of the extension methods overloads of BulkInsertValuesIntoTempTableAsync/BulkInsertIntoTempTableAsync to create a temp table and to insert data into it.

Remarks: The SqlConnection/SqliteConnection is opened before creation of a temp table automatically. The connection must stay open the whole time because a temp table becomes inaccessible after the connection is closed.

Bulk insert of value and complex objects into temp tables with default settings:

// 1 column
List<Guid> ids = ...;
await using var tempTable = await ctx.BulkInsertValuesIntoTempTableAsync(ids);

IQueryable<Guid> query = tempTable.Query;


// 2 columns
List<(Guid customerId, Guid productId)> tuples = ...;
await using var tempTable = await ctx.BulkInsertValuesIntoTempTableAsync(tuples);

IQueryable<TempTable<Guid, Guid>> query = tempTable.Query;


// custom entity
List<Customer> customersToInsert = ...;
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(customersToInsert);

IQueryable<Customer> query = tempTable.Query;

The returned object tempTable is of type ITempTableQuery<T>. The ITempTableQuery<T> is an IAsyncDisposable/IDisposable so the caller can Dispose (i.e. DROP) the temp table and close the previously opened connection.

Remarks: Closing the connection by disposing the ITempTableQuery<T> is a good practice, especially if the lifetime of the DbContext is managed manually and not by DI.
But, if the temp table is being used during a request of an ASP.NET Core application then forgetting to dispose the temp table is not critical in most cases because the instances of DbContexts (and the DbConntection inside them) are very short-lived and will be disposed of automatically at the end of the request. After that, the disposed DbConnection is added back to internal connection pool and is being reset before reuse so the next request don't have access to the temp tables from the previous one.

Bulk Insert Options

Use the corresponding implementation of ITempTableBulkInsertOptions to configure the creation of the temp tables and the insertion of values/entities into temp table.

• SQL Server: SqlServerTempTableBulkInsertOptions
• SQLite: SqliteTempTableBulkInsertOptions

Temp tables with a subset of properties

By default, the temp table is created for all properties of an entity. You can use the options to specify the columns to insert.

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

   // required so the temp table is created with selected properties only
   Advanced = { UsePropertiesToInsertForTempTableCreation = true }
};

await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(customersToInsert, options);

// we may access included properties only
var query = await tempTable.Query.Select(c => new { c.Id, c.FirstName, c.LastName });

Primary Key Creation

By default, a primary key is created if the corresponding entity has a primary key defined. Use the options to change this behavior.

var options = new SqlServerTempTableBulkInsertOptions
{
   PrimaryKeyCreation = IPrimaryKeyPropertiesProvider.EntityTypeConfiguration // default
};

await ctx.BulkInsertIntoTempTableAsync(customersToInsert, options);

Alternatives are:

• None: No primary key is created.
• EntityTypeConfiguration: The primary key is created according the entity configuration.
• AdaptiveEntityTypeConfiguration: Creates a primary key according to the configuration of the entity. Properties are skipped which are not part of the actual temp table.
• AdaptiveForced: Creates a primary key according to the configuration of the entity. If the entity is keyless then all its properties are used for creation of the primary key. Columns which are not part of the actual temp table are skipped.
• A custom implementation of IPrimaryKeyPropertiesProvider.

When using this feature with MS SQL Server then you can specify when the primary key should be created: BeforeBulkInsert or AfterBulkInsert (default).

var options = new SqlServerTempTableBulkInsertOptions
{ 
    MomentOfPrimaryKeyCreation = MomentOfSqlServerPrimaryKeyCreation.AfterBulkInsert // default
};

await ctx.BulkInsertIntoTempTableAsync(customersToInsert, options);

Typical SqlBulkCopy options (SQL Server)

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

Reuse of the temp table within the same connection

var options = new SqlServerTempTableBulkInsertOptions
{
  DropTableOnDispose = false,
  TruncateTableIfExists = true
};

Recommendations

• Use keyless entities for temp tables, otherwise EF enables change tracking. To prevent unexpected behavior, the temp table query (i.e. IQueryable<T>) will come with AsNoTracking().
• Use new classes specifically made for temp tables instead of (re)using the real entities.

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

Owned Entity Types

Owned entity types are not supported.

SplitQuery

The temp tables cannot be used in queries with QuerySplittingBehavior.SplitQuery.