Efficient way to do bulk insert/update with Entity Framework
entity-framework
Question
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.
Accepted Answer
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.
Popular Answer
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)
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
