EF Core Bulk Insert Optimize Entity Framework Insert Performance

Description

The EF BulkInsert extension method let you insert a large number of entities in your database.

// Easy to use
context.BulkInsert(customers);

// Easy to customize
context.BulkInsert(invoices, options => options.IncludeGraph = true);

Try it in EF Core | Try it in EF6

Performance Comparison

Try it in EF Core | Try it in EF6

HINT:

A lot of factors might affect the benchmark time such as index, column type, latency, throttling, etc.

Scenarios

The BulkInsert method is fast but also flexible to let you handle various scenarios in Entity Framework such as:

• Insert and keep identity value
• Insert and include/exclude properties
• Insert only if the entity not already exists
• Insert with related child entities (Include Graph)
• Insert with future action
• Insert without returning identity value
• More scenarios

What is supported?

• All Entity Framework Core Version: EF Core 7, EF Core 6, EF Core 5, EF Core 3
• All Entity Framework Version: EF6, EF5, EF4
• All Inheritances (TPC, TPH, TPT)
• Complex Type/Owned Entity Type
• Enum
• Value Converter (EF Core)
• And more!

Advantages

• Easy to use
• Flexible
• Increase performance
• Increase application responsiveness
• Reduce database load
• Reduce database round-trips

Getting Started

Bulk Insert

The BulkInsert and BulkInsertAync methods extend your DbContext to let you insert a large number of entities in your database.

context.BulkInsert(customers);

context.BulkInsertAsync(customers, cancellationToken);

Try it in EF Core | Try it in EF6

Bulk Insert with options

The options parameter let you use a lambda expression to customize the way entities are inserted.

context.BulkInsert(customers, options => options.BatchSize = 100);

context.BulkInsert(customers, options => { 
    options.InsertIfNotExists = true;
    options.PrimaryKeyExpression = customer => customer.Code;
  });

Try it in EF Core | Try it in EF6

Why BulkInsert is faster than SaveChanges?

Inserting thousands of entities for an initial load or a file importation is a typical scenario.

The SaveChanges method makes it quite slow/impossible to handle this kind of situation due to the number of database round-trips required. The SaveChanges perform one database round-trip for every entity to insert. So, if you need to insert 10,000 entities, 10,000 database round-trips will be performed which is INSANELY slow.

The BulkInsert in counterpart requires the minimum number of database round-trips possible. For example, under the hood for SQL Server, a SqlBulkCopy is performed to insert 10,000 entities which is the fastest way available.

Real Life Scenarios

Insert and keep identity value

Your entity has an identity property, but you want to force to insert a specific value instead. The InsertKeepIdentity option allows you to keep the identity value of your entity.

context.BulkInsert(customers, options => options.InsertKeepIdentity = true);

Try it in EF Core | Try it in EF6

Insert and include/exclude properties

You want to insert your entities but only for specific properties.

• ColumnInputExpression: This option let you choose which properties to map.
• IgnoreOnInsertExpression: This option let you ignore properties that are auto-mapped.

context.BulkInsert(customers, options => options.ColumnInputExpression = c => new { c.CustomerID, c.Name} );
            
context.BulkInsert(customers, options => options.IgnoreOnInsertExpression = c => new { c.ColumnToIgnore } );

Try it in EF Core | Try it in EF6

Insert only if the entity does not already exists

You want to insert entities but only those that don't already exist in the database.

• InsertIfNotExists: This option let you insert only entities that doesn't already exists.
• PrimaryKeyExpression: This option let you customize the key to use to check if the entity already exists or not.

context.BulkInsert(customers, options => {
    options.InsertIfNotExists = true;
    options.ColumnPrimaryKeyExpression = c => c.Code;
});

Try it in EF Core | Try it in EF6

Insert with related child entities (Include Graph)

You want to insert entities but also automatically insert related child entities.

• IncludeGraph: This option lets you to automatically insert all entities part of the graph.
• IncludeGraphBuilder: This option lets you customize how to insert entities for a specific type.

context.BulkInsert(invoices, options => options.IncludeGraph = true);

Try it in EF Core | Try it in EF6

Insert with future action

You want to insert entities, but you want to defer the execution.

By default, BulkInsert is an immediate operation. That means, it's executed as soon as you call the method.

FutureAction: This option let you defer the execution of a Bulk Insert. ExecuteFutureAction: This option trigger and execute all pending FutureAction.

context.FutureAction(x => x.BulkInsert(customers));
context.FutureAction(x => x.BulkInsert(invoices, options => options.IncludeGraph = true));

// ...code...

context.ExecuteFutureAction();

Try it in EF Core | Try it in EF6

Insert without returning identity value

By default, the EF BulkInsert method already returns the identity when inserting.

However, such behavior impacts performance. For example, when the identity must be returned, a temporary table is created in SQL Server instead of directly using SqlBulkCopy into the destination table.

You can improve your performance by turning off the AutoMapOutput option.

context.BulkInsert(customers, options => options.AutoMapOutputDirection = false);

Try it in EF Core | Try it

More scenarios

Hundreds of scenarios have been solved and are now supported.

The best way to ask for a special request or to find out if a solution for your scenario already exists is by contacting us: info@zzzprojects.com

Documentation

BulkInsert

Methods

Options

More options can be found here:

• Audit
• Batch
• Column
• Context Factory
• Execute Event
• Identity
• Include Graph
• Key
• Logging
• Temporary Table
• Transaction
• Transient Error
• SQL Server

Limitations

Hidden Navigation (EF6 only)

The BulkInsert doesn't use the ChangeTracker to optimize the performance unless no other alternative exists.

For example, you want to insert InvoiceItem but there is no relation toward the parent Invoice. In this case, you will need to add your entities in the ChangeTracker. The ChangeTracker will be used to find the related Invoice for your InvoiceItem.

try
{
    context.BulkInsert(items);
}
catch
{
    Console.WriteLine("An error is throw because the Invoice relation cannot be found.");
}

context.Invoices.AddRange(invoices);

// The ChangeTracker is used for this case
context.BulkInsert(items);

Try it in EF6