dbcontext.SaveChanges() memory exception in large records EF

Question

I am facing out of memory exception while executing decontext.savechanges() method.

Actually i need to insert/update 10000+ record into some tables.

ie

//insert table 1 // 10000 record <br>
//insert or update record to table 2 //25000 record<br>
//table 3 1000 record<br>

....
.....
.....

db.context.savechanges(); //Exception happening in this line.

I got "Exception of type 'System.OutOfMemoryException' was thrown" error.
I tried AutoDetectChangesEnabled but its did not help

mydbcontext.Configuration.AutoDetectChangesEnabled = false;
mydbcontext.SaveChanges();

may be the attribute vale range exed .please show attribute values — LDS, Jul 11 '20 at 16:49
Why not try using Bulk Insert? I believe it is better in performance too — Bosco, Jul 11 '20 at 17:31
@Bosco Bulk upload is not practical in my case. I need this way to solve my issue. — Jishnu Chandran, Jul 11 '20 at 17:36
@LDS the insertion of my table depending on some conditions . — Jishnu Chandran, Jul 11 '20 at 17:37
where use the savechanges ,whether inside the loop or after add the entity outside the loop — LDS, Jul 11 '20 at 17:44
@LDS i have called save changes after adding all entity outside — Jishnu Chandran, Jul 12 '20 at 06:42
call it inside the loop means after each row insert call savechanges — LDS, Jul 12 '20 at 12:37
@LDS I need a method something like context.BatchSaveChanges(); — Jishnu Chandran, Jul 12 '20 at 18:32
@Bosco any sample method simile to context.BatchSaveChanges(); — Jishnu Chandran, Jul 12 '20 at 18:33

score 1 · Answer 1 · answered Jul 13 '20 at 12:35

"Exception of type 'System.OutOfMemoryException' was thrown" - This normally happens to me when I don't dispose my database. The issue probably isn't the amount of records you are trying to update, its probably because those records are kept in the System Memory.

Solution 1(Recommended) Wrap your database functions in 'using' statements which will auto dispose your database context and free up memory

public void CreateRange(List<MyModel> modelList){
    using(DbContext db = new DbContext())
    {
       db.MyTable.AddRange(modelList); 
       db.SaveChanges();
    }
}

Solution 2 Call 'db.Dispose();' before the end of each method

public void CreateRange(List<MyModel> modelList){
    db.MyTable.AddRange(modelList); 
    db.SaveChanges();
    db.Dispose();
}

Thanks, I got the similar issue, when migrate data, I update many time with SaveChanges() call, however, I didn't Dispose() after the saving, at the beginning, it seems OK, fast, after for a while, it becomes slow, then I got OutOfMemory exception, my total memory cost at 97%. — Box Very, Apr 07 '22 at 07:54

score 0 · Answer 2 · edited Jul 13 '20 at 12:53

context.BulkInsert(entitiesList);

For BulkInsert follow the link https://github.com/borisdj/EFCore.BulkExtensions

Another is sqlBulkCopy Fastest Way of Inserting in Entity Framework

If you want to use the Savechanges outsude the loop, so look the below code

try
{
    context.Configuration.AutoDetectChangesEnabled = false;
 
    foreach (var book in bookList)
    {
        context.Books.Add(book);
    }
    
    context.SaveChanges();
}
finally 
{
    context.Configuration.AutoDetectChangesEnabled = true;
}

dbcontext.SaveChanges() memory exception in large records EF

2 Answers2