Bulk insert with EF

Question

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

I don't think that using Entity Framework for bulk import is efficient, have you considered using `SqlBulkCopy`? Also, can you please add a code example of how you add the entities to the context? — elolos, Oct 08 '14 at 08:00
Rather than "Detach", try turning off change tracking - AutoDetectChangesEnabled with: context.Configuration.AutoDetectChangesEnabled = false; — reckface, Oct 08 '14 at 08:10
@reckface context.Configuration.AutoDetectChangesEnabled is not available in .net 3.5 — Buda Gavril, Oct 08 '14 at 08:34
What version of EF do you have? Is this available: context.tblMyTable.MergeOption = MergeOption.NoTracking; ? — reckface, Oct 08 '14 at 09:24
I have EF V1 (I'm using .net 3.5) and context.tblMyTable.MergeOption = MergeOption.NoTracking; it's available. Thanks — Buda Gavril, Oct 08 '14 at 11:21

score 2 · Answer 1 · answered Oct 08 '14 at 08:03

2

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();
  }
}

answered Oct 08 '14 at 08:03

BRAHIM Kamel

12,559
30
46

I don't have the method BulkInsert on my db object. Is this available in .NET 3.5 ? – Buda Gavril Oct 08 '14 at 08:09
is not a .net 3.5 method you have to download https://efbulkinsert.codeplex.com/ and compile it for 3.5 and then you will get the Extension method for 3.5 – BRAHIM Kamel Oct 08 '14 at 08:11
1

@BudaGavril you can also get it via nuget nuget `Install-Package EntityFramework.BulkInsert-efx` (replace x with version number 4, 5 or 6) – DrCopyPaste Oct 08 '14 at 08:12
I am not allowed to use third party libraries, only the classes from .net 3.5 framework – Buda Gavril Oct 08 '14 at 08:23
ok so download the source code and make it yours if you want :) – BRAHIM Kamel Oct 08 '14 at 08:45
this would mean that all the source code from that library would need to go trough code review and this can take time – Buda Gavril Oct 08 '14 at 11:26
I don't think so it will need just the time to compile as the library is in stable release and is faraway from a serious critical security vulnerabilities if this was your concern – BRAHIM Kamel Oct 08 '14 at 11:55

reckface · Accepted Answer · 2014-10-08T11:45:41.190

2

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

edited Oct 08 '14 at 11:45

answered Oct 08 '14 at 08:04

reckface

4,908
3
33
59

If I'm creating new context object after db.SaveChanges(), I'm getting the exception "An entity object cannot be referenced by multiple instances of IEntityChangeTracker" – Buda Gavril Oct 08 '14 at 11:23
My recommendation can't be used with the pattern you have: AddToContext(DBModelContainer db... To keep the active context graph small means, creating a context, adding the entities, calling save changes and disposing of it (using context = new DBModelContainer... I'll expand on this in the answer – reckface Oct 08 '14 at 11:40
If I'm using this approach, I call save for every item and it will take more time to add all the items... – Buda Gavril Oct 08 '14 at 11:55
Have you tried it? Have you read that blog post? Using the long running context, took 57 minutes for some 30,000 adds, shrinking the scope of the context went down to 33 seconds. The principles discussed there will apply to your situation. – reckface Oct 08 '14 at 12:00
If I'm creating a new context object in my for loop, I'm getting the error An entity object cannot be referenced by multiple instances of IEntityChangeTracker. at System.Data.Objects.DataClasses.EntityObject.System.Data.Objects.DataClasses.IEntityWithChangeTracker.SetChangeTracker(IEntityChangeTracker changeTracker) But If I'm using using (db) { db.tblMyTable.MergeOption = MergeOption.NoTracking; db.AddTotblMyTable(tr); db.SaveChanges(); } – Buda Gavril Oct 08 '14 at 12:09
I'm getting the error The ObjectContext instance has been disposed and can no longer be used for operations that require a connection. at System.Data.Objects.ObjectContext.get_Connection() – Buda Gavril Oct 08 '14 at 12:10
and I need to use the context object outside my for loop to read some stuff from the database – Buda Gavril Oct 08 '14 at 12:10
Ok, I think you've got all you need to decide on a way forward. A long running context with no tracking or a narrow scoped context. http://msdn.microsoft.com/en-gb/data/jj729737.aspx warns: As you load more objects and their references into memory, the memory consumption of the context may increase rapidly... – reckface Oct 08 '14 at 12:20

score 0 · Answer 3 · answered Oct 08 '14 at 07:53

0

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

answered Oct 08 '14 at 07:53

Nilesh Thakkar

1,392
3
21
31

the count variable is the number of objects generated to be inserted. As you can see, only when I've added 10000 of reached to the total number of objects to generate I call saveChanges(), not for every item. What is Unit Of Work? – Buda Gavril Oct 08 '14 at 07:57
1

@BudaGavril, EF DbContext is an implementation of the Unit of Work pattern: http://www.wekeroad.com/2014/03/04/repositories-and-unitofwork-are-not-a-good-idea/ – elolos Oct 08 '14 at 07:59
AddRange may help to save time DataContext.Entity.AddRange(entityEnumerable); – Nilesh Thakkar Oct 08 '14 at 08:04
the method db.tblMyTables.AddRange(entityEnumerable) it's not available, I'm using EF V1 (.net 3.5) – Buda Gavril Oct 08 '14 at 11:30

Bulk insert with EF

3 Answers3

Linked