6

Using Entity Framework 4.3.1 Database First, what is a good way to commit/save object changes to the database frequently? In the below, I'd like to save the invoice immediately after the quickbooks call, and not risk waiting for all invoices to be posted. But, I cannot call SaveChanges each time in the loop, it will throw an exception.

It would be handy to have a .Save() method on each object, perhaps there's a good way to do that?

var unpostedInvoices = entities.GetUnpostedInvoices();
foreach (Invoice invoice in unpostedInvoices)
{
    // this takes a long time
    var invoiceDto = quickbooks.PostInvoice(invoice);

    invoice.Posted = true;
    invoice.TransactionId = invoiceDto.TransactionId;

    // I'd like to save here rather than after the foreach loop, but this will fail
    //entities.SaveChanges();
}

// this works, but I don't want to risk waiting this long to save
entities.SaveChanges();

This is the exception thrown when calling SaveChanges() in the loop.

New transaction is not allowed because there are other threads running in the session.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName)
at System.Data.SqlClient.SqlInternalConnection.BeginTransaction(IsolationLevel iso)
at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
at System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
RyanW
  • 5,338
  • 4
  • 46
  • 58

2 Answers2

7

This question might help: https://stackoverflow.com/questions/2113498

You cannot start a new transaction while you are still reading a result set and SaveChanges creates a transaction if one does not already exist.

One solution is to complete the read first, then iterate over the in-memory result set.

If you change this line:

var unpostedInvoices = entities.GetUnpostedInvoices().ToList();

... can you put SaveChanges back inside the loop?

Community
  • 1
  • 1
Nick Butler
  • 24,045
  • 4
  • 49
  • 70
  • I still don't get why we cannot start a new transaction while we are still reading a result set. What's the techinical reason? – Charlie Apr 12 '19 at 01:23
2

EF will keep track of all the changes and it will update the DB when you call SaveChanges(). I would not then call SaveChanges() within a loop(even if it should not fail).

Bear in mind EF makes a separate database roundtrip for every record you want to insert, update or delete so it generally doesn't matter how often do you call SaveChanges. Avoiding this is mostly possible only when using direct SQL and creating single SqlCommand executing all inserts at once.

Anyway this error is due to Entity Framework creating an implicit transaction during the SaveChanges() call

using (var transaction = new TransactionScope())
{
    using (var context = new MyContext())
    {
      foreach (Invoice invoice in unpostedInvoices)
        {
            // Change to invoice
            context.SaveChanges();
        }
    }
    transaction.Complete();
}
Massimiliano Peluso
  • 26,379
  • 6
  • 61
  • 70
  • Yes, I want to issue the update (database round trip) each time through the loop rather than all at the end. But, it's not working to call SaveChanges() each time. Since I use sprocs for inserts and updates, I will look at just doing a function import on the update and calling it directly on the context. – RyanW Jun 06 '12 at 14:08
  • Thanks, that makes sense. In this case, I want each update to live on its own though and not be subject to rollback if an invoice later in the loop fails. – RyanW Jun 06 '12 at 17:27