Temp Tables

Required Nuget Package:

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

Configuration
Usage
- All-in-one: create and insert
- Two-step: create then insert separately
Bulk Insert Options
Temp Table Creation Options
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();
                                                            })
                               // PostgreSQL
                               //.UseNpgsql("conn-string", npgsqlOptions =>
                               //                          {
                               //                                npgsqlOptions.AddBulkOperationSupport();
                               //                          })
                               // SQLite
                               //.UseSqlite("conn-string", sqliteOptions =>
                               //                          {
                               //                                sqliteOptions.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

Remarks: The database connection 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.

All-in-one: create and insert

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

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.

The ITempTableQuery<T> also exposes NumberOfInsertedRows which reports how many rows were inserted into the temp table:

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

int insertedCount = tempTable.NumberOfInsertedRows;

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.

Two-step: create then insert separately

Use CreateTempTableAsync to create an empty temp table first, and then insert data into it separately using one of the BulkInsertIntoTempTableAsync/BulkInsertValuesIntoTempTableAsync overloads that accept an ITempTableReference. This approach is useful when you need more control over the table lifecycle, for example if you want to insert data in multiple batches.

Creating an empty temp table

// Generic overload (recommended)
var options = new TempTableCreationOptions();
await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

string tableName = tempTable.Name; // the actual name of the temp table in the database

The returned object tempTable is of type ITempTableReference. Like ITempTableQuery<T>, it implements IAsyncDisposable/IDisposable to drop the temp table and close the previously opened connection on dispose. However, unlike ITempTableQuery<T>, it does not provide a Query or NumberOfInsertedRows property.

Use the corresponding provider-specific options for provider-specific settings:

// SQL Server: adds "COLLATE database_default" to string columns
var options = new SqlServerTempTableCreationOptions
{
   UseDefaultDatabaseCollation = true
};

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

// PostgreSQL: collation handling
var options = new NpgsqlTempTableCreationOptions
{
   SplitCollationComponents = false
};

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

// SQLite: uses the base TempTableCreationOptions
var options = new TempTableCreationOptions();

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

Creating a temp table with a subset of columns

Use PropertiesToInclude to create a temp table containing only specific columns:

var options = new TempTableCreationOptions
{
   PropertiesToInclude = IEntityPropertiesProvider.Include<Customer>(c => new { c.Id, c.FirstName })
};

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

Inserting data into an existing temp table

Once you have an ITempTableReference, use the overloads that accept a tempTable parameter to insert data:

var options = new TempTableCreationOptions();
await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

// Insert data (returns Task, not ITempTableQuery<T>)
List<Customer> batch1 = ...;
await ctx.BulkInsertIntoTempTableAsync(batch1, tempTable);

// Insert more data into the same temp table
List<Customer> batch2 = ...;
await ctx.BulkInsertIntoTempTableAsync(batch2, tempTable);

// For values (primitives / tuples)
List<Guid> ids = ...;
await using var valueTable = await ctx.CreateTempTableAsync<TempTable<Guid>>(new TempTableCreationOptions());
await ctx.BulkInsertValuesIntoTempTableAsync(ids, valueTable);

Remarks: The overloads that insert into an existing temp table return Task (not Task<ITempTableQuery<T>>), since the table already exists and the caller already holds a reference to it.

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
PostgreSQL: NpgsqlTempTableBulkInsertOptions
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 PropertiesToInsert to specify which columns to insert.

When inserting only a subset of properties, you should also set UsePropertiesToInsertForTempTableCreation to true so that the temp table is created with only the selected columns:

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 });

UsePropertiesToInsertForTempTableCreation

The Advanced.UsePropertiesToInsertForTempTableCreation setting controls whether the temp table structure matches the selected properties or the full entity:

true (recommended when using PropertiesToInsert): The temp table is created with only the selected columns. Excluded columns do not exist in the temp table, so there are no NOT NULL constraint issues.
false (default): The temp table is created with all columns from the entity, but only the selected columns are populated during the bulk insert. Excluded columns receive NULL values. If any excluded column has a NOT NULL constraint (e.g., a non-nullable value type like int or a required string property), the database will throw a constraint violation error.

For example, given an entity with a required Count column (type int, which is always NOT NULL in the database), using PropertiesToInsert to insert only the Id column with UsePropertiesToInsertForTempTableCreation = false will fail:

// This will FAIL with a NOT NULL constraint violation because the temp table
// has all columns (including the NOT NULL "Count" column) but only "Id" is inserted.
var options = new SqlServerTempTableBulkInsertOptions
{
   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => c.Id),
   Advanced = { UsePropertiesToInsertForTempTableCreation = false } // default
};

// Throws: "Cannot insert the value NULL into column 'Count'..." (SQL Server)
//         "NOT NULL constraint failed..." (SQLite)
//         "null value in column ... violates not-null constraint" (PostgreSQL)
await ctx.BulkInsertIntoTempTableAsync(customers, options);

To avoid this, set UsePropertiesToInsertForTempTableCreation = true so the temp table only contains the columns you are inserting:

// This works because the temp table only has the "Id" column.
var options = new SqlServerTempTableBulkInsertOptions
{
   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => c.Id),
   Advanced = { UsePropertiesToInsertForTempTableCreation = true }
};

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

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
};

Collation handling (PostgreSQL)

When creating temp tables for string columns that have a custom collation, the collation is included in the column definition. By default, collation strings containing a . are interpreted as schema.collation_name and each component is escaped separately (e.g., myschema.ci becomes COLLATE "myschema"."ci").

If your collation name itself contains a dot and should not be split, set SplitCollationComponents to false. In that case the entire collation string is escaped as a single identifier (e.g., myschema.ci becomes COLLATE "myschema.ci").

// Via NpgsqlTempTableCreationOptions (when creating temp tables directly)
var creationOptions = new NpgsqlTempTableCreationOptions
{
   SplitCollationComponents = false // default is true
};

await using var tempTable = await tempTableCreator.CreateTempTableAsync(entityType, creationOptions);

// Via NpgsqlTempTableBulkInsertOptions (when using bulk insert into temp tables)
var bulkInsertOptions = new NpgsqlTempTableBulkInsertOptions
{
   SplitCollationComponents = false // default is true
};

await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(entities, bulkInsertOptions);

// Via NpgsqlBulkOperationTempTableOptions (when using bulk update/upsert operations)
var updateOptions = new NpgsqlBulkUpdateOptions
{
   TempTableOptions = { SplitCollationComponents = false }
};

await ctx.BulkUpdateAsync(entities, updateOptions);

Temp Table Creation Options

When using CreateTempTableAsync (the two-step approach), use ITempTableCreationOptions / TempTableCreationOptions to configure how the temp table is created. These options are separate from ITempTableBulkInsertOptions, which combines both creation and insertion settings for the all-in-one approach.

Property	Default	Description
`TruncateTableIfExists`	`false`	Truncates/drops the temp table if it exists already. If `false`, no EXISTS check is performed (the table is assumed to be new).
`TableNameProvider`	`ReusingTempTableNameProvider`	Controls temp table naming. Use `DefaultTempTableNameProvider` for unique (GUID-based) names.
`PrimaryKeyCreation`	`EntityTypeConfiguration`	Controls whether and how a primary key is created. See Primary key creation.
`PropertiesToInclude`	`null` (all properties)	Create the temp table with only a subset of the entity's columns.
`DropTableOnDispose`	`true`	Whether to drop the temp table when the returned `ITempTableReference` is disposed.

Provider-specific additions:

SQL Server (SqlServerTempTableCreationOptions): UseDefaultDatabaseCollation — adds COLLATE database_default to string columns.
PostgreSQL (NpgsqlTempTableCreationOptions): SplitCollationComponents — controls collation string escaping (see Collation handling).

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.

Temp Tables

Configuration

Enable temp table support

Configure Temp Tables

Usage

All-in-one: create and insert

Two-step: create then insert separately

Creating an empty temp table

Creating a temp table with a subset of columns

Inserting data into an existing temp table

Bulk Insert Options

Temp tables with a subset of properties

UsePropertiesToInsertForTempTableCreation

Primary Key Creation

Typical SqlBulkCopy options (SQL Server)

Reuse of the temp table within the same connection

Collation handling (PostgreSQL)

Temp Table Creation Options

Recommendations

Limitations

Default values

MS SQL Server

SQLite

Shadow Properties

Owned Entity Types

SplitQuery

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Performance

Features

Convenience

Integration Testing

Extensibility

Clone this wiki locally