DbConnection Closed After SaveChanges In EF6

Question

I get some data from a source and I want to add them in the database, so I open a context for each batch and use a transaction to commit the changes after all the data was added.

My code:

      using (var conn = new SqlConnection(dataAccessSettings.DatabaseConnectString))
      {
        conn.Open();

        using (var sqlTxn = conn.BeginTransaction(System.Data.IsolationLevel.Snapshot))
        {
          foreach (var dataBatch in GetData())
          {
            using (var context = new DataContext(conn, false))
            {
              context.Database.UseTransaction(sqlTxn);
              context.DataTable.AddRange(dataBatch);

              context.SaveChanges();
            }
          }
          sqlTxn.Commit();
        }
      }

However, when the second context calls the context.Database.UseTransaction(sqlTxn) I get the following error:

The transaction passed in must have a non-null connection. A null connection indicates the transaction has already been completed.

I set the contextOwnsConnection constructor parameter of the context to false so what exactly is closing my connection?

have you tried moving the using (var context = new DataContext(conn, false)) before foreach and see if the error gone? — Maulana Mango, Jul 02 '20 at 07:17
Yes, it works in that case but I want to dispose the context after each batch so I don't fill the memory. — Mark Uivari, Jul 02 '20 at 08:02
You have to make sure the dispose of DataContext doesn't clear the sqlTxn saved in its context instance then. — Maulana Mango, Jul 02 '20 at 13:19
`context` should be used outside the for loop, so that `SaveChanges` is not executed in for loop multiple times. — Hemal, Jul 04 '20 at 07:27
I cannot load all the elements in the context at once because that would fill the available memory. — Mark Uivari, Jul 06 '20 at 08:26

score 0 · Answer 1 · answered Jul 04 '20 at 07:23

You must remove SaveChanged in loop statement.

If you call it inside the loop, EF will write back the changes to the database for every single entity (and every entity will be in its own, separate transaction), One call with 50 changes is typically much better / faster / more efficient than 50 calls for 1 change each.

Change Code for it

      using (var conn = new SqlConnection(dataAccessSettings.DatabaseConnectString))
  {
    conn.Open();

    using (var sqlTxn = conn.BeginTransaction(System.Data.IsolationLevel.Snapshot))
    {
      foreach (var dataBatch in GetData())
      {
        using (var context = new DataContext(conn, false))
        {
          context.Database.UseTransaction(sqlTxn);
          context.DataTable.AddRange(dataBatch);
          // remove SaveChanges here
        }
      }
      sqlTxn.Commit();
      context.SaveChanges();
    }
  }

At that point the context is already disposed. Also I add multiple elements in a SaveChanges, not just one. — Mark Uivari, Jul 06 '20 at 08:25
[work with trnc ](https://learn.microsoft.com/en-us/ef/ef6/saving/transactions) — Zanyar Jalal, Jul 08 '20 at 21:03

score 0 · Answer 2 · answered Jul 04 '20 at 13:59

based off : Using Transactions or SaveChanges(false) and AcceptAllChanges()?

using (var conn = new SqlConnection(dataAccessSettings.DatabaseConnectString))
using (var context = new BloggingContext(conn, false)) 
{ 
    using (var dbContextTransaction = context.Database.BeginTransaction()) 
    { 
        try 
        { 
            foreach (var dataBatch in GetData())
            {
                context.Database.UseTransaction(sqlTxn);
                context.DataTable.AddRange(dataBatch);
            }

            context.SaveChanges(); 

            dbContextTransaction.Commit(); 
        } 
        catch (Exception) 
        { 
            dbContextTransaction.Rollback(); //Required according to MSDN article 
            throw; //Not in MSDN article, but recommended so the exception still bubbles up
        } 
    } 
} 
}

DbConnection Closed After SaveChanges In EF6

2 Answers2