Entity Framework Slow AddRange Insert to DB

Question

I am implementing a Database First Entity Framework 6.1 to insert about 2000 master/detail collections to SQL server db. Each collection has about two to three objects. So the the total number or records to be inserted is ~5000. The transaction takes about two to three minutes which is very slow. I am using the below code:

    public class Collection
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }

        ...

        public List<DetailCol> Details{ get; set; } 
    }


    public class DetailCol
    {
            [Key]
            public int Id { get; set; }
            public decimal Lvl { get; set; }
            public string Type { get; set; }
    }

    var MyCollections = new List<Collection>();
    
    // Do population of collection and try to insert to db

    using (var db = new MyContext(ConnectionString)
    {
        // Speed up insert
        db.Configuration.AutoDetectChangesEnabled = false;
    
        // Add new entries to master and details
        db.MyCollections.AddRange(MyCollections);
    
        // Update db
        db.SaveChanges();
    }

Can someone give a hint why this is happening and how to improve that?

Answer 1

I just went through this same thing, and AddRange() takes minutes to add just 1200 records in EF 6.1 since it is doing INSERTS one at a time when you look at the profiler, so BulkCopy is going to be your fastest option. The good news is for basic Table inserts you can use an extension called EntityFramework.BulkInsert by installing this nuget package: EFBulkInsert

Add: using EntityFramework.BulkInsert.Extensions; Then do something like this:

var options = new BulkInsertOptions();
options.BatchSize = 1000; // Default is 5000
efContext.BulkInsert<MyObject>(myList, options);
efContext.SaveChanges();

I went from minutes to 2-3 seconds, and this was even from my computer to an Azure SQL Database.