Bulk insert from a csv file using Entity Framework

Question

I want to do bulk insert from a csv file into the MS SQL Server database using entity framework. Is there any inbuilt method for Bulk Insert using Entity Framework?

Thanks

Answer 1

There is no support of bulk insert in Entity framework. If you try to add thousands of objects simply adding them to DbSet and then call SaveChanges you will have significant drop of the performance.

using(var db = new Db()) 
{
    foreach(var item from data) 
    {
        db.Items.Add(item);
    }
    db.SaveChanges();
}

It happens because EF tracks all the changes of every object which results with big memory usage and high calculations.

Depending on the EF version and approach you use you can disable tracking changes, insert objects by relatively small parts of 100 or 1000 items, depending on their size and actual performance in your case. Then calculate changes once and save. For example.

int count = 100;
using (var db = new BloggingContext()) 
{ 
    data.Configuration.AutoDetectChangesEnabled = false; 
    foreach (var item in data) 
    { 
        db.Items.Add(item);
        --count;
        if(count <= 0) 
        {
            count = 100;
            db.SaveChanges();
        }
    } 
    db.SaveChanges(); // To make sure we save everything if the last part was smaller than 100
}

Also there are few libraries which support bulk inserts. For example: https://github.com/loresoft/EntityFramework.Extended

Answer 2

As Yaroslav commented, there is no direct support to Bulk Insert from Entity Framework Team,

However 3 libraries support it:

• Entity Framework Extensions (Recommended, support everything)
• EntityFramework.BulkInsert (Unsupported, but support all simple scenarios)
• EntityFramework.BulkUtilities (Unsupported, but support some simple scenarios)

Here you can find a small article about the three libraries: Entity Framework - Bulk Insert

Example:

using (var ctx = new EntitiesContext())
{
    // Do not use the ChangeTracker or require to add the list in the DbSet
    ctx.BulkInsert(list);
}

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