Memory error when entering millions of records using Entity Framework

Question

I am using the Entity Framework to Insert and Update Thousands of Records.

At first it was slow, but after putting the code down it improved the speed.

db.Configuration.AutoDetectChangesEnabled = false;  
db.Configuration.ValidateOnSaveEnabled = false;

Speed Solved.

However, I can not reach the end because, as you insert, the model is getting large and increasing memory consumption until give me exception to reach 1,5GB of use.

Note: I have tried using the AsNoTracking().

I’m also trying to recharge the model from time to time, but it doesn’t lower consumption. It only increases.

Has anyone gone through this or has any idea?

Part of the code:

foreach (var prd in produtoGradeAux)
{
    if (dbPdv.Database.Connection.State != ConnectionState.Open)
       dbPdv.Database.Connection.Open();

using (var transaction = dbPdv.Database.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
    dbPdv.Database.UseTransaction(transaction);
    i++
    Produto prodAux = null;
    var pAux = dbPdv.produto_grade.AsNoTracking().FirstOrDefault(x => x.produto_GradeIdGw == prd.produto_gradeID);

    if (prd.cd_grade.Trim().Length > 6)
        pAux = dbPdv.produto_grade.AsNoTracking().FirstOrDefault(x => x.cd_grade.Trim() == prd.cd_grade.Trim());

    if (prd.cd_grade.Trim().Length > 6)
        prodAux = dbPdv.produto_grade.AsNoTracking().Where(x => x.Produto.cd_ref.Trim() == prd.cd_grade.Trim().Substring(0, 6)).Select(x => x.Produto).FirstOrDefault();

    int lnFamiliaId, lnGrupoId, lnUnidadeId, lnMarcaId, lnLinhaId;

    RetGrupos(prd, out lnFamiliaId, out lnGrupoId, out lnUnidadeId, out lnMarcaId, out lnLinhaId, dbPdv);

    if (pAux == null)
    {
        if (prodAux == null)
            prodAux = RetProduto(dbPdv, prd.Produto, lnFamiliaId, lnGrupoId, lnUnidadeId, lnMarcaId, lnLinhaId);
        pAux = RetProdutoGrade(dbPdv, prodAux, prd);
        SetProdutoEan(dbPdv, prd, pAux);
        SetProdutoCf(dbPdv, prd, pAux);
        SetProdutoEstoque(dbPdv, lojaAux, prd, pAux);
        SetProdutoPreco(dbPdv, prd, pAux);
    }
    else
    {
        AtuProdutoGrade(dbPdv, prd, pAux);
        AtuProduto(dbPdv, prd, pAux, lnFamiliaId, lnGrupoId, lnUnidadeId, lnMarcaId, lnLinhaId);
        AtuProdutoEan(dbPdv, prd, pAux);
        AtuProduto_Cf(dbPdv, prd, pAux);
        AtuProduto_Preco(dbPdv, prd, pAux);
        AtuProdutoEstoque(dbPdv, lojaAux, prd, pAux);
    }

    transaction.Commit();

    //Tentar Melhorar performance...
    if (i % 1000 == 0)
    {
        HabilitaDb(dbPdv);
        dbPdv.Dispose();
        dbPdv = GetDbPdv(pdvAux);
        DesabilitaDb(dbPdv);
    }
}  
}

Answer 1

The Entity Framework was not made to enter 1 million records... It has an internal memory limit...(index dele) Asnotracking() will not solve... I have already performed the most complex tests possible.

For this type of insertion part for a ORM minor... with less management and details... in case a Microorm, as an example Dapper, Petapoco, etc...

If not feasible, use the Bulk Insert, which is an ADO.net implementation for the Entity framework. Entityframework.Bulkinsert

In my opinion, I recommend using in such cases the ADO.net for these processes that require performance in a large amount of data transition. Taking connection data from the context of the Entity framework or config. Because you are not limited only in INSERT(bulkinsert), you can perform other operations. A business option, in a large system, is to go to the Nhibernate. By personal experience and tests performed.

---

If so Entity Framework Core, see that link

Answer 2

In this answer, I explain several alternatives you can use to improve performance, but I do not believe that, even used together, they will solve your problem completely.

There are some things that caught my attention in your code. For example:

if (dbPdv.Database.Connection.State != ConnectionState.Open)
   dbPdv.Database.Connection.Open();

This is not necessary. The Entity Framework itself is responsible for controlling the connection lifecycle.

This:

using (var transaction = dbPdv.Database.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
    dbPdv.Database.UseTransaction(transaction);
    ...

It is also not aligned with the good practices of the Entity Framework (distributed transaction support). The correct thing would be to open one TransactionScope with the option of ReadUncommited in the IsolationLevel:

using (var scope = new TransactionScope(TransactionScopeOption.Required,
            new TransactionOptions()
            {
                IsolationLevel = IsolationLevel.ReadUncommitted
            }))
{
    ...

For your case, @Daniloloko’s answer is the way forward. The Nuget package from Entityframework.Bulkinsert is here.