Optimization/correction in "Dbcontext.Savechanges()"

Question

having regard to the following scenario::

using (TransactionScope scope = new TransactionScope())
{
    var allObjects = new List<MyObject>();
    var objects = myStaticClass.GetAllObjects();

    foreach (var myObject in allObjects)
        dbContext.MyObject.Add(myObject);

    dbContext.SaveChanges();

    scope.Complete();
}

In the loop I have about 250.000 records to be processed, that is, those so many to be added to a DbSet to be stored in the database at once through the SaveChanges() of DbContext.

The problem here is the amount of objects to be added to DbSet, that at a certain point causes a System.OutOfMemoryException (hits close to 2Gb of occupied memory), this because the architecture of the process is x86, something I cannot change.

---

What I need, even urgently, is a solution to this case, that is, I need to store the 250.000 records but I have to avoid the System.OutOfMemoryException to let the process end.

Answer 1

Excuse me you were right there was a simpler solution and it did not involve used memory count:

foreach (var myObject in allObjects)
{
     try{

        dbContext.MyObject.Add(myObject);

     } catch (OutOfMemoryException){
        // Se der erro por falta de memória:
        // Salve as alterações já feita
        dbContext.SaveChanges();
        // Força uma coleta de lixo imediata de todas as gerações.
        GC.Collect();
        // Refaz a última operação.
        dbContext.MyObject.Add(myObject);
     } catch (Exception){
       // tratamento para outros tipos de exceção
     }
}
dbContext.SaveChanges();

Answer 2

I managed to solve the problem with a more "raw" solution, where I basically do the INSERT directly in the database without going through EPH:

using (TransactionScope scope = new TransactionScope())
{
    StringBuilder sb = new StringBuilder();
    var allObjects = myStaticClass.GetAllObjects();

    int count = allObjects.Count;
    bool split = count > 100;

    for (int i = 0; i < allObjects.Count; i++)
    {
        MyObject myObject = allObjects[i];

        sb.AppendLine("INSERT INTO MyObjects(Id, Column1, Column2, Column3)");
        sb.AppendLine($"VALUES('{myObject.Id}', '{myObject.Column1}', '{myObject.Column2}', '{myObject.Column3}')");

        if ((split && i > 0 && (i % 100) == 0) || (i + 1) == count)
        {
            dbContext.Database.ExecuteSqlCommand(sb.ToString());
            sb = new StringBuilder();
        }
    }

    if (sb.Length > 0)
    {
        dbContext.Database.ExecuteSqlCommand(sb.ToString());
        sb = null;
    }

    scope.Complete();
}

I came to the conclusion that the real problem was EPH, that in addition to increasing memory consumption also impaired performance almost gradually.