Error entering data into database, EF 6

Asked

Viewed 1,179 times

5

I’m trying to input data into the database, but I have a mistake in doing the saveChanges(); after doing several times the context.add(), has happened after 500, 2200 and 5500 times.

Error:

The transaction associated with the current connection has been completed, but has not been dropped. The transaction must be dropped before using the connection to execute the SQL statements.

I have a function that inserts the data:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
{
    Contexto context = null;
    try
    {
        context = new Contexto();
        context.Configuration.AutoDetectChangesEnabled = false;

        int count = 0;            
        for (int i = 0; i < textoLido.count; i++)
        {
            //criando instancia da entidade 
            // adicionando os dados nela;

            ++count;
            context = AddToContext(context, entidade, count, 100, true);
        }

        context.SaveChanges();
    }
    finally
    {
        if (context != null)
        context.Dispose();
    }

    scope.Complete();
    }

And a function that recreates the context after so many additions in the context:

private Contexto AddToContext<Entity>(Contexto context,
Entity entity, int count, int commitCount, bool recreateContext) where Entity : class
{
    context.Set<Entity>().Add(entity);

    if (count % commitCount == 0)
    {
        //erro acontece aqui
        context.SaveChanges();

        if (recreateContext)
        {
            context.Dispose();
            context = new Contexto();
            context.Configuration.AutoDetectChangesEnabled = false;
        }
    }

   return context;
}

How can I make this insertion work or it won’t work? Thanks in advance!

c# .net entity-framework-6

  • Out of curiosity, what would be, on average, the size of the textoLido?

  • 15 thousand lines, 9 columns, 135 thousand "records".

  • Have you tried putting some USING there in your code?

  • 3

    lol why do you say that? I solved this problem using the Entity framework’s Insert bulks in an api

  • the slow execution and not causing this not? The transaction with the bank is dying at some point and ends up discarding this context. Place a Sleep to force a delay between Savechanges or modify the Connection connection pool and timeout to simulate.

1 answer

2


As stated by comment, the correct way to resolve it is by using the Entityframework.Bulkinsert, that has Nuget package.

An example of use would be:

using EntityFramework.BulkInsert.Extensions;

private Contexto AddToContext<Entity>(Contexto context, Entity entity, int count, int commitCount) where Entity : class
{
    context.Set<Entity>().BulkInsert(entity);

    if (count % commitCount == 0)
    {
        //erro acontece aqui
        context.SaveChanges();

        // Isto não precisa
        // if (recreateContext)
        // {
        //    context.Dispose();
        //    context = new Contexto();
        //    context.Configuration.AutoDetectChangesEnabled = false;
        // }
    }

   return context;
}

Browser other questions tagged c# .net entity-framework-6

You are not signed in. Login or sign up in order to post.