LinqToSql Tutorial Bulk Operations

When you want to insert, delete or update hundreds, thousands, or millions of entities using LINQ to SQL SubmitChanges() method, you will notice that your application performance is INSANELY slow.

• The SubmitChanges() requires one database round-trip for every entity to insert, delete or update.
• So if you need to add, remove or update 10000 entities, then 10000 database round-trips will be performed, and your application suffers from performances issues.

To improve your application performance, you can use a 3rd party library LinqToSql Plus. It dramatically improves LinqToSql performances by using bulk operations.

Bulk Operations

LinqToSql Plus provides the following extension methods which require the minimum database round-trips as compared to SubmitChanges(). By example under the hood for SQL Server, a simple SqlBulkCopy could be performed. These methods give you additional flexibility by allowing to customize options such as primary key, columns, include childs entities and more.

• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge (UPSERT operation)
• BulkSynchronize

BulkInsert

It inserts all entities in the database.

using (var context = new CustomerDataContext(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=CustomerDB;"))
{
    // Easy to use
    context.BulkInsert(list);

    // Easy to customize
    context.BulkInsert(list, options => options.BatchSize = 100);
}

Read more: BulkInsert

BulkUpdate

It updates all entities in the database. All rows that match the entity key are considered as existing and are UPDATED in the database.

using (var context = new CustomerDataContext(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=CustomerDB;"))
{
    // Easy to use
    context.BulkUpdate(list);

    // Easy to customize
    context.BulkUpdate(customers, options => options.ColumnPrimaryKeyExpression = customer => customer.Code);
}

Read more: BulkUpdate

BulkDelete

It deletes all entities from the database. All rows that match the entity key are DELETED from the database.

using (var context = new CustomerDataContext(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=CustomerDB;"))
{
    // Easy to use
    context.BulkDelete(list);

    // Easy to customize
    context.BulkDelete(customers, options => options.ColumnPrimaryKeyExpression = customer => customer.Code);
}

Read more: BulkDelete

BulkMerge

A merge is an UPSERT operation. All rows that match the entity key are considered as existing and are UPDATED, other rows are considered as new rows and are INSERTED in the database.

using (var context = new CustomerDataContext(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=CustomerDB;"))
{
    // Easy to use
    context.BulkMerge(list);

    // Easy to customize
    context.BulkMerge(customers, options => options.ColumnPrimaryKeyExpression = customer => customer.Code);
}

Read more: BulkMerge

BulkSynchronize

It synchronizes all entities to the database. It is a mirror operation from the data source to the database. All rows that match the entity key are UPDATED, non-matching rows that exist from the source are INSERTED, non-matching rows that exist in the database are DELETED.

The database table becomes a mirror of the entity list provided.

using (var context = new CustomerDataContext(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=CustomerDB;"))
{
    // Easy to use
    context.BulkSynchronize(list);

    // Easy to customize
    context.BulkSynchronize(customers, options => options.ColumnPrimaryKeyExpression = customer => customer.Code);

Read more: BulkSynchronize

Performance Comparisons