CODEWITHSUNDEEP
c#entity-frameworkdatabase-performance
Buda Gavril
Buda Gavril

Reputation: 21637

Bulk insert with EF

I need to insert some objects (about 4 million) in the database using C# and EF (using .NET 3.5). My method that adds the objects is in a 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 detach the added objects (of type tblMyTable) from the context object? I don't need them for a later use and when more than 300000 objects are added, the execution time between db saving ( db.SaveChanges()) increases considerably.

Regards

Upvotes: 0

Views: 5212

Answers (3)

reckface
reckface

Reputation: 5858

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

  • Keep the active Context Graph small by using a new context for each Unit of Work
  • Turn off AutoDetechChangesEnabled - context.Configuration.AutoDetectChangesEnabled = false;
  • Batching, in your loop, Call SaveChanges periodically

EDIT

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

Keeping a long running db context is not advisable, so consider refactoring your Add method to not keep attempting to reuse the same context.

See Rick Strahl's post on bulk inserts for more details

Upvotes: 2

BRAHIM Kamel
BRAHIM Kamel

Reputation: 13765

AFAK EF does not support directly the BulkInsert so it will be tedious to do such thing 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();
  }
}

Upvotes: 2

Nilesh Thakkar
Nilesh Thakkar

Reputation: 1459

You may try Unit Of Work and dont save context (SaveChanges) on every record insert but save it at end

Upvotes: 0

Related Questions