Fastest way of inserting many parent and child records

Question

I've found this article helpful in doing batch inserts Fastest Way of Inserting in Entity Framework, however, I'm wondering if there is a more efficient way of inserting records with child entities as well.

My entities are like this:

I've been experimenting with BulkInsert and it seems to improve performance a bit, however it is still about 60 seconds for about 40 records each having multiple child entities. I have cases where I'll need to insert thousands of records and it can get extremely slow. My code is as follows:

using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 15, 0)))
{
    using (var db = new MyDBContext())
    {
        db.Configuration.AutoDetectChangesEnabled = false;
        var entities = db.ScenarioCategory.AddRange(categories);
        db.BulkInsert(entities);
        db.SaveChanges();
     }
     transactionScope.Complete();
}

Why are you calling both `AddRange` and `BulkInsert`? You should pick one of them, because they both do the same operation.. — shlatchz, Apr 13 '16 at 18:09
@SteveGreene From BulkInsert: "To combine bulk insert with DbContext, TransactionScope must be used." — tqrecords, Apr 13 '16 at 19:16

Jonathan Magnan · Accepted Answer · 2018-08-18T16:39:09.167

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

Here is the fastest way of inserting, updating, deleting, and merging. You can even make it easier and use BulkSaveChanges over SaveChanges.

// Using BulkSaveChanges
using (var db = new MyDBContext())
{
    db.ScenarioCategory.AddRange(categories);
    db.BulkSaveChanges();
}

// Using BulkInsert on parent then child
using (var db = new MyDBContext())
{
    db.BulkInsert(categories);
    db.BulkInsert(categories.SelectMany(x => x.Items);
}

Fastest way of inserting many parent and child records

1 Answers1