Efficient way to do bulk insert/update with Entity Framework

I have a list of entities and I want to insert them into a database. If the entity already exists in the database as is then it needs to be skipped. If its in the database but with different values then it needs to be updated.

Is there any way to do this other than do a db call per item?

My plan is to try an insert, if a unique constraint exception on the key is thrown then do an update.

Solution 1:

Just don't use Entity Framework in this case. Just use a stored procedure (how to depends on the version/approach you use with EF, you might will have to extend your DbContext or add a mapping from the entity model).

If you're using SQL Server, then in your store procedure, do use the MERGE command that efficiently does exactly what you need: insert if it doesn't exist, or update if it does. Everything in a single, efficient SQL query.

Solution 2:

EF isnt suited to BULK inserts. For 1000s of records it ok, but large numbers (100k plus) its slow.

If you are planning to use EF.

• try AddOrUpdate method , (instead of insert/Update)
• Disable tracking,
• commit every 1000 records or fewer.

eg

Context.Set<TPoco>().AddOrUpdate(poco);
//...
Context.Configuration.AutoDetectChangesEnabled =
//..
Context.SaveChanges();

If copying unrelated data you can try those tables in parallel (doh)

Solution 3:

I have made an extension for that https://efbulkinsert.codeplex.com/

and it is really simple to use

using(var context = new MyDbContext())
{
    context.BulkInsert(hugeCollectionOfEntities);
}

Solution 4:

1. Create a temp table: SqlCommand(string.Format("SELECT TOP 0 * INTO {0} FROM {1}...

2. Bulk insert data into it -- Entity Framework Extended mentioned above would need to be tweaked to support the temp table name but otherwise is on the right track -- or roll a bit of code and use SqlBulkCopy.

3. Construct a MERGE statement.

If you mine a property list you can make (2) and (3) generic. I can read and merge 150,000 rows in about 20 seconds.