EF Codefirst Bulk Insert

Question

I need to insert around 2500 rows using EF Code First.

My original code looked something like this:

foreach(var item in listOfItemsToBeAdded)
{
    //biz logic
    context.MyStuff.Add(i);
}

This took a very long time. It was around 2.2 seconds for each DBSet.Add() call, which equates to around 90 minutes.

I refactored the code to this:

var tempItemList = new List<MyStuff>();
foreach(var item in listOfItemsToBeAdded)
{
    //biz logic
    tempItemList.Add(item)
}
context.MyStuff.ToList().AddRange(tempItemList);

This only takes around 4 seconds to run. However, the .ToList() queries all the items currently in the table, which is extremely necessary and could be dangerous or even more time consuming in the long run. One workaround would be to do something like context.MyStuff.Where(x=>x.ID = *empty guid*).AddRange(tempItemList) because then I know there will never be anything returned.

But I'm curious if anyone else knows of an efficient way to to a bulk insert using EF Code First?

Your second example doesn't actually perform any inserts. The 4 seconds is all just pulling the data across the wire - the tempItemList is added to a new `List` that you're creating, and not added to the context itself... — Reed Copsey, Sep 03 '13 at 19:57
@Reed But once I call SaveChanges() and query MyStuff from Management Studio I have the 2500 rows inserted into the database. — James, Sep 03 '13 at 20:00
@James - I think that you are concluding incorrectly, perhaps you are looking at data from a previous run, because as far as I can tell, Reed is correct. The second version is adding objects to a temporary list in memory, not the DbSet itself — Pete, Sep 03 '13 at 21:06

score 13 · Accepted Answer · edited May 23 '17 at 11:46

13

Validation is normally a very expensive portion of EF, I had great performance improvements by disabling it with:

context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;

I believe I found that in a similar SO question--perhaps it was this answer

Another answer on that question rightly points out that if you really need bulk insert performance you should look at using System.Data.SqlClient.SqlBulkCopy. The choice between EF and ADO.NET for this issue really revolves around your priorities.

edited May 23 '17 at 11:46

Community

1
1

answered Sep 03 '13 at 20:46

STW

44,917
17
105
161

This is something I was thinking about but I am also doing updates and it could effect those. – James Sep 03 '13 at 21:08

score 2 · Answer 2 · answered Sep 03 '13 at 20:33

2

I have a crazy idea but I think it will help you.

After each adding 100 items call SaveChanges. I have a feeling Track Changes in EF have a very bad performance with huge data.

answered Sep 03 '13 at 20:33

Bassam Alugili

16,345
7
52
70

2

That might be true depending on the machine running the code, but in my experience calling `SaveChanges()` too often is more of a performance penalty than calling it as little as possible – STW Sep 03 '13 at 20:45
2

I'm using Repository and UnitOfWork pattern when I add/delete a lot of data I do not do it in one bulk but I devide the changes in small related Groups and calling SaveChanges after finishing each group after that I dispose the unitOfWork to free the memory. 200 GB DB I can free it with 10 mins if I do all the changes in one block i Need hours for that. – Bassam Alugili Sep 03 '13 at 21:01

score 2 · Answer 3 · answered Jun 20 '14 at 03:04

I would recommend this article on how to do bulk inserts using EF.

Entity Framework and slow bulk INSERTs

He explores these areas and compares perfomance:

Default EF (57 minutes to complete adding 30,000 records)
Replacing with ADO.NET Code (25 seconds for those same 30,000)
Context Bloat- Keep the active Context Graph small by using a new context for each Unit of Work (same 30,000 inserts take 33 seconds)
Large Lists - Turn off AutoDetectChangesEnabled (brings the time down to about 20 seconds)
Batching (down to 16 seconds)
DbTable.AddRange() - (performance is in the 12 range)

Jonathan Magnan · Answer 4 · 2018-02-27T14:34:03.300

As STW pointed out, the DetectChanges method called every time you call the Add method is VERY expensive.

Common solution are:

Use AddRange over Add
SET AutoDetectChanges to false
SPLIT SaveChanges in multiple batches

See: Improve Entity Framework Add Performance

It's important to note that using AddRange doesn't perform a BulkInsert, it's simply invoke the DetecthChanges method once (after all entities is added) which greatly improve the performance.

But I'm curious if anyone else knows of an efficient way to to a bulk insert using EF Code First

There is some third party library supporting Bulk Insert available:

See: Entity Framework Bulk Insert library

Disclaimer: I'm the owner of Entity Framework Extensions

This library allows you to perform all bulk operations you need for your scenarios:

Bulk SaveChanges
Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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 Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

score 1 · Answer 5 · answered Sep 03 '13 at 20:59

EF is not really usable for batch/bulk operations (I think in general ORMs are not).

The particular reason why this is running so slowly is because of the change tracker in EF. Virtually every call to the EF API results in a call to TrackChanges() internally, including DbSet.Add(). When you add 2500, this function gets called 2500 times. And each call gets slower and slower, the more data you have added. So disabling the change tracking in EF should help a lot:

dataContext.Configuration.AutoDetectChangesEnabled = false;

A better solution would be to split your big bulk operation into 2500 smaller transactions, each running with their own data context. You could use msmq, or some other mechanism for reliable messaging, for initiating each of these smaller transactions.

But if your system is build around a lot a bulk operations, I would suggest finding a different solution for your data access layer than EF.

score 1 · Answer 6 · answered Sep 15 '16 at 16:17

While this is a bit late and the answers and comments posted above are very useful, I will just leave this here and hope it proves useful for people who had the same problem as I did and come to this post for answers. This post still ranks high on Google (at the time of posting this answer) if you search for a way to bulk-insert records using Entity Framework.

I had a similar problem using Entity Framework and Code First in an MVC 5 application. I had a user submit a form that caused tens of thousands of records to be inserted into a table. The user had to wait for more than 2 and a half minutes while 60,000 records were being inserted.

After much googling, I stumbled upon BulkInsert-EF6 which is also available as a NuGet package. Reworking the OP's code:

var tempItemList = new List<MyStuff>();
foreach(var item in listOfItemsToBeAdded)
{
    //biz logic
    tempItemList.Add(item)
}

using (var transaction = context.Transaction())
{
    try
    {
        context.BulkInsert(tempItemList);
        transaction.Commit();
    }
    catch (Exception ex)
    {
        // Handle exception
        transaction.Rollback();
    }
}

My code went from taking >2 minutes to <1 second for 60,000 records.

score 1 · Answer 7 · answered Oct 16 '17 at 21:49

Although a late reply, but I'm posting the answer because I suffered the same pain. I've created a new GitHub project just for that, as of now, it supports Bulk insert/update/delete for Sql server transparently using SqlBulkCopy.

https://github.com/MHanafy/EntityExtensions

There're other goodies as well, and hopefully, It will be extended to do more down the track.

Using it is as simple as

var insertsAndupdates = new List<object>();
var deletes = new List<object>();
context.BulkUpdate(insertsAndupdates, deletes);

Hope it helps!

score 0 · Answer 8 · answered Sep 04 '13 at 07:58

0

EF6 beta 1 has an AddRange function that may suit your purpose:

INSERTing many rows with Entity Framework 6 beta 1

EF6 will be released "this year" (2013)

answered Sep 04 '13 at 07:58

Colin

22,328
17
103
197

score 0 · Answer 9 · answered Jan 06 '17 at 03:42

    public static void BulkInsert(IList list, string tableName)
    {
        var conn = (SqlConnection)Db.Connection;
        if (conn.State != ConnectionState.Open) conn.Open();

        using (var bulkCopy = new SqlBulkCopy(conn))
        {
            bulkCopy.BatchSize = list.Count;
            bulkCopy.DestinationTableName = tableName;

            var table = ListToDataTable(list);
            bulkCopy.WriteToServer(table);
        }
    }

    public static DataTable ListToDataTable(IList list)
    {
        var dt = new DataTable();
        if (list.Count <= 0) return dt;

        var properties = list[0].GetType().GetProperties();
        foreach (var pi in properties)
        {
            dt.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
        }

        foreach (var item in list)
        {
            DataRow row = dt.NewRow();
            properties.ToList().ForEach(p => row[p.Name] = p.GetValue(item, null) ?? DBNull.Value);
            dt.Rows.Add(row);
        }
        return dt;
    }

EF Codefirst Bulk Insert

9 Answers9

Linked