.NET Bulk Operations Bulk Insert

Description

The BulkInsert method lets you insert a large number of entities in your database.

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

// Easy to customize
bulk.DestinationTableName = "Customers";
bulk.InsertIfNotExists = true;
bulk.AutoMapOutputIdentity = true;
bulk.BulkInsert(customers);

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

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

Performance View

Operations 1,000 Entities 2,000 Entities 5,000 Entities
BulkInsert 50 ms 55 ms 75 ms

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

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

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

Advantages

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

Getting Started

Bulk Insert

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

bulk.BulkInsert(customers);

bulk.BulkInsertAsync(customers, cancellationToken);

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

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

Bulk Insert with options

The options parameter lets you customize the way entities are inserted.

bulk.BatchSize = 100;
bulk.BulkInsert(customers);

bulk.PrimaryKeyExpression = customer => customer.Code;
bulk.InsertIfNotExists = true;
bulk.BulkInsert(customers);

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

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

Real-Life Scenarios

Insert and keep identity value

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

bulk.DestinationTableName = "Customers";
bulk.InsertKeepIdentity = true;
bulk.BulkInsert(customers);

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

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

Insert and include/exclude properties

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

  • ColumnInputExpression: This option lets you choose which properties to map.
  • IgnoreOnInsertExpression: This option lets you ignore properties that are auto-mapped.
bulk.ColumnInputExpression = c => new { c.CustomerID, c.Name};
bulk.BulkInsert(customers);
            
bulk.IgnoreOnInsertExpression = c => new { c.ColumnToIgnore };
bulk.BulkInsert(customers);

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

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

Insert only if the entity not already exists

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

  • InsertIfNotExists: This option lets you insert only entity that doesn't already exist.
  • PrimaryKeyExpression: This option lets you customize the key to use to check if the entity already exists or not. This option disables the Auto Mapping.
  • AutoMapKeyExpression: This option lets you customize the key with expression and keep the Auto Mapping.
  • AutoMapKeyName: This option lets you customize the key by names and keep the Auto Mapping.
bulk.InsertIfNotExists = true;
bulk.AutoMapKeyExpression = c => c.Code;
bulk.BulkInsert(customers);

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

bulk.InsertIfNotExists = true;
bulk.AutoMapKeyName = "Code";
bulk.BulkInsert(customers);

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

You want to insert related child entities.

bulk.AutoMapOutputIdentity = true;
bulk.BulkInsert(invoices);

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

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

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

Insert with returning identity value

By default, the BulkInsert method doesn't return the identity when inserting.

You can return the identity by specifying it should be returned.

bulk.AutoMapOutputIdentity = true;
bulk.BulkInsert(customers);

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

Try it (Entity): .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

BulkInsert

Methods
Name Description Example (DataTable) Example (Entity)
BulkInsert<T>(items) Bulk insert entities in your database. .NET Core / .NET Framework .NET Core / .NET Framework
BulkInsertAsync<T>(items) Bulk insert entities asynchronously in your database.
BulkInsertAsync<T>(items, cancellationToken) Bulk insert entities asynchronously in your database.
Options

More options can be found here:



Contents