.NET Bulk Operations Bulk Merge

Description

The BulkMerge method lets you merge (insert or update/Upsert) a large number of entities in your database.

// Easy to use
bulk.DestinationTableName = "Customers";
bulk.BulkMerge(customers);

// Easy to customize
bulk.DestinationTableName = "Customers";
bulk.BatchSize = 100;
bulk.AutoMapOutputIdentity = true;
bulk.BulkMerge(customers);

Try it (Entity): .NET Core | .NET Framework

Try it (DataTable): .NET Core | .NET Framework

Performance Comparison

Operations 1,000 Entities 2,000 Entities 5,000 Entities
BulkMerge 80 ms 110 ms 170 ms

Try this benchmark (Entity): .NET Core | .NET Framework

Try this benchmark (DataTable): .NET Core | .NET Framework

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 such as:

Advantages

  • Easy to use
  • Flexible
  • Increase performance
  • Increase application responsiveness

Getting Started

Bulk Merge

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

bulk.BulkMerge(customers);

bulk.BulkMergeAsync(customers, cancellationToken);

Try it (Entity): .NET Core | .NET Framework

Try it (DataTable): .NET Core | .NET Framework

Bulk Merge with options

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

bulk.AutoMapKeyExpression = c => c.Code;
bulk.BulkMerge(customers);

Try it (Entity): .NET Core | .NET Framework

bulk.AutoMapKeyName = "Code";
bulk.BulkMerge(customers);

Try it (DataTable): .NET Core | .NET Framework

Real-Life Scenarios

Merge and keep identity value

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

bulk.MergeKeepIdentity = true;
bulk.BulkMerge(customers);

Try it (Entity): .NET Core | .NET Framework

Try it (DataTable): .NET Core | .NET Framework

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 lets you ignore properties only for the INSERT part.
  • IgnoreOnMergeUpdateExpression: This option lets you ignore properties only for the UPDATE part.
bulk.ColumnInputExpression = c => new { c.CustomerID, c.Name};
bulk.BulkMerge(customers);
            
bulk.IgnoreOnMergeUpdateExpression = c => new { c.CreatedDate };
bulk.BulkMerge(customers);

Try it (Entity): .NET Core | .NET Framework

var columnMapping = new ColumnMapping("CreatedDate");
                
columnMapping.IgnoreOnMergeUpdate = true
                    
bulk.ColumnMappings.Add("CustomerID", true);
bulk.ColumnMappings.Add("UpdatedDate");
bulk.ColumnMappings.Add("Name");
bulk.ColumnMappings.Add(columnMapping);
bulk.BulkMerge(dtCustomers);

Try it (DataTable): .NET Core | .NET Framework

Merge with custom key

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

bulk.AutoMapKeyExpression = c => c.Code;
bulk.BulkMerge(customers);

Try it (Entity): .NET Core | .NET Framework

bulk.AutoMapKeyName = "Code";
bulk.BulkMerge(customers);

Try it (DataTable): .NET Core | .NET Framework

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

bulk.DestinationTableName = "Invoices";
bulk.AutoMapOutputIdentity = true;
bulk.BulkMerge(invoices);

// SET foreign key value            
invoices.ForEach(x => x.Items.ForEach(y => y.InvoiceID = x.InvoiceID));

bulk.DestinationTableName = "InvoiceItems";
bulk.BulkMerge(invoices.SelectMany(x => x.Items).ToList());

Try it (Entity): .NET Core | .NET Framework

Try it (DataTable): .NET Core | .NET Framework

More scenarios

Hundred of scenarios has 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
Name Description Example (DataTable) Example (Entity)
BulkMerge<T>(items) Bulk insert entities in your database. .NET Core / .NET Framework .NET Core / .NET Framework
BulkMergeAsync<T>(items) Bulk insert entities asynchronously in your database.
BulkMergeAsync<T>(items, cancellationToken) Bulk insert entities asynchronously in your database.
Options

More options can be found here:



Contents