Bulk insert with EF

Question

I need to insert some objects (about 4 million) into the database using C # and EF (using .NET 3.5). My method that adds objects is in: for

private DBModelContainer AddToContext(DBModelContainer db, tblMyTable item, int count) { db.AddTottblMyTable (item); if ((count % 10000== 0) || (count == this.toGenerate)) { try { db.SaveChanges(); } catch (Exception e) { Console.WriteLine(e.StackTrace); } } return db; } 

How to separate added objects (such as tblMyTable) from a context object? I do not need them for later use, and when more than 300,000 objects are added, the runtime between saving db (db.SaveChanges ()) increases significantly.

Hi

Answer 1

Entity Framework may not be the best tool for this type of operation. You might be better off with a simple ADO.Net, some stored procedures ... But if you needed to use it, here are a few suggestions:

• Keep the active context chart small by using a new context for each Unit of Work
• Disable AutoDetechChangesEnabled - context.Configuration.AutoDetectChangesEnabled = false;
• Grouping in a loop Periodically call SaveChanges

EDIT

  using(var db = new DBModelContainer()) { db.tblMyTable.MergeOption = MergeOption.NoTracking; // Narrow the scope of your db context db.AddTottblMyTable (item); db.SaveChanges(); } 

Maintaining a long-term db context is not practical, so consider reorganizing your Add method so that it does not continue to repeat the use of the same context.

See Rick Strahl Bulk Insert Report for more details.

Answer 2

AFAK EF does not directly support BulkInsert, so it will be tedious to do this manually.

try to consider EntityFramework.BulkInsert

 using (var ctx = GetContext()) { using (var transactionScope = new TransactionScope()) { // some stuff in dbcontext ctx.BulkInsert(entities); ctx.SaveChanges(); transactionScope.Complete(); } } 

Answer 3

You can try Unit of Work and not save the context (SaveChanges) in each record, but save it at the end