Bulk MergeEasily perform add or update (Upsert) operations in EF Core

The BulkMerge method lets you merge entities using your EF Core model into your database. This kind of merge operation is often called “Upsert,” “Add or Update,” or “Insert or Update.” There is currently no direct equivalent to BulkMerge built into EF Core.

When you perform a bulk merge, it behaves as follows:

• Rows that match the entity key are UPDATED.
• Rows that do not match any existing record are INSERTED.

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

// Easy to customize
context.BulkMerge(customers, options => options.IncludeGraph = true);

Online Example (EF Core) | Online Example (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 BulkMerge method is fast but also flexible to let you handle various scenarios in Entity Framework such as:

• Merge and keep identity value
• Merge with custom key
• Merge and include/exclude properties
• Merge with related child entities (Include Graph)
• Merge with future action
• More scenarios

What is supported?

• All Entity Framework Core Version: EF Core 9, EF Core 8, 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 Merge

The BulkMerge and BulkMergeAync methods extend your DbContext to let you merge a large number of entities in your database.

context.BulkMerge(customers);

context.BulkMergeAsync(customers, cancellationToken);

Try it in EF Core | Try it in EF6

Bulk Merge with options

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

context.BulkMerge(customers, options => options.ColumnPrimaryKeyExpression = c => c.Code });

Try it in EF Core | Try it in EF6

Why BulkMerge is faster than SaveChanges?

Merging thousands of entities for a file importation is a typical scenario.

The AddOrUpdate method performs a database round-trip for every entity to check if it already exists. The DetectChanges change method is also called for every entity which makes this method even slower (it's like using the Add method instead of AddRange).

The SaveChanges method performs one database round-trip for every entity to update.

So, if you need to merge 10,000 entities, 20,000 database round-trips will be performed + 10,000 DetectChanges calls which is INSANELY slow.

The BulkMerge in counterpart requires the minimum number of database round-trips possible. For example, under the hood for SQL Server, a SqlBulkCopy is performed first in a temporary table, then a MERGE from the temporary table to the destination table is performed which is the fastest way available.

Real Life Scenarios

Merge and keep identity value

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

context.BulkMerge(customers, options => options.MergeKeepIdentity = true);

Try it in EF Core | Try it in EF6

Merge and include/exclude properties

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

• ColumnInputExpression: This option lets you choose which properties to map.
• ColumnIgnoreExpression: This option lets you ignore properties that are auto-mapped.
• IgnoreOnMergeInsertExpression: This option let you ignore properties only for the INSERT part.
• IgnoreOnMergeUpdateExpression: This option let you ignore properties only for the UPDATE part.

context.BulkMerge(customers, options => options.ColumnInputExpression = c => new { c.CustomerID, c.Name} );
            
context.BulkMerge(customers, options => options.IgnoreOnMergeUpdateExpression = c => new { c.UpdatedDate } );

Try it in EF Core | Try it in EF6

Merge with custom key

You want to merge entities, but you don't have the primary key. The ColumnPrimaryKeyExpression let you use as a key any property or combination of properties.

context.BulkMerge(customers, options => options.ColumnPrimaryKeyExpression = c => c.Code);

Try it in EF Core | Try it in EF6

Merge with related child entities (Include Graph)

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

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

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

Try it in EF Core | Try it in EF6

Merge with future action

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

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

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

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

// ...code...

context.ExecuteFutureAction();

Try it in EF Core | Try it in EF6

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

BulkMerge

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