Entity Framework bulk insert with transaction management - Performance

Question

I followed the answer in the following link for improving the performance of entity framework for bulk inserts and updates.

Improving bulk insert performance in Entity framework

1. Made save changes in batch of 1000
2. regeneration of the DbContext object after every save changes

I got the performance boost as expected.

But I have a requirement of rolling back the transaction while exception condition while performing bulk insert.

This becomes issue when we re initialize the DbContext object.

Is there any other way to use the transaction and rollback with the same setup under the exception condition?

Any help will be appreciated

Answer 1

Transaction

If you want to rollback changes, you will need to use a transaction.

You can share the same connection within all context you generate.

Here is a small example:

try
{
    var connection = new SqlConnection("[ConnectionString]");
    var trans = connection.BeginTransaction();

    while (condition)
    {
        using (TestContext context = new TestContext(connection))
        {
             // ...code..
        }
    }

    trans.Commit();
}
catch
{
    // ...code..
}

public class TestContext : DbContext
{
    public TestContext(SqlConnection connection) : base(connection, false)
    {

    }

    // ...code...
}

Bulk Insert

You are not performing a BulkInsert. You are currently fixing a performance issue with the DetectChanges methods that's called every time you use the Add method.

See: http://entityframework.net/improve-ef-add-performance

If you have 50,000 entities to insert, you are still performing 50,000 database round-trip which is INSANELY slow.

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library is a paid library but let you to really perform BulkInsert.

Only a few database round-trip will be required

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Bulk Operations
context.BulkInsert(customers, options => {
   options => options.IncludeGraph = true;
});
context.BulkMerge(customers, options => {
   options.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

EDIT: Answer Comment

the proposed library indeed looks great. But can you please elaborate regarding the transaction management with bulk operations?

Sure,

Documentation: http://entityframework-extensions.net/transaction

BulkSaveChanges

As SaveChanges, BulkSaveChanges already save all entities within an internal transaction. So by default, there is nothing to do.

However, if you start a transaction within Entity Framework, BulkSaveChanges will honor it and will use this transaction instead of creating an internal transaction.

var transaction = context.Database.BeginTransaction();
try
{
    context.BulkSaveChanges();
    transaction.Commit();
}
catch
{
    transaction.Rollback();
}

Bulk Operations

Bulk Operations such as BulkInsert, BulkUpdate, BulkDelete doesn’t use a transaction by default. This is your responsibility to handle it.

If you start a transaction within Entity Framework, Bulk Operations will honor it.

var transaction = context.Database.BeginTransaction();
try
{
    context.BulkInsert(list1);
    context.BulkInsert(list2);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
}