8

We are in a process of gradually replacing legacy data access code by entity framework (4.3.1). In some occasions we can't avoid using both ways of data access in one unit of work. Ideally, this should be done in one transaction. However, the old code uses SqlTransactions that call Commit() when a unit of work is done and EF manages its own transactions.

So we thought of wrapping "old" and "new" code in a TransactionScope. However, a Commit within a surrounding TransactionScope is always executed, even if the TransactionScope is not completed. This code snippet illustrates my problem:

using (var conn = new SqlConnection("connection string"))
{
  conn.Open();
  using (var scope = new TransactionScope())
  {
    using (var tr = conn.BeginTransaction())
    {
      using (var cmd = conn.CreateCommand())
      {
        cmd.Transaction = tr;
        cmd.CommandText = "some update statement";
        cmd.ExecuteNonQuery();
      }
      tr.Commit();
    }
    // In reality the code above is part of a legacy DAL, immutable.
    // (can't insert SaveChanges before tr.Commit).
    context.SaveChanges();
    if (<all ok>) // pseudo code for exception handling.
        scope.Complete(); 
  }
}

The update statement is still committed when scope.Complete() is not hit.

So as it seems, I can not use TransactionScope to force the old data access code and a SaveChanges from a context to execute in one transaction. Or is there a way to overrule the SqlTransaction.Commit statement?

I know that there are more posts here about TransactionScope and SqlTransaction, but they all (rightly) say that using SqlTransaction is not necessary (nor recommended) when using TransactionScope. But not using SqlTransaction is not an option here. We have a legacy framework that commits its own SqlTransactions and that has no api to hook into its transaction mechanism.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291

1 Answers1

9

The update statement is still committed when scope.Complete() is not hit.

Oh no!! The TransacationScope Is Not Being Used1.

Auto-enlist only works if the Connection is open after (or inside) the TransactionScope.

Putting the Open inside the TransactionScope should fix this issue (even with the manual transaction?) as the Connection will then [usually] auto-enlist in the ambient TS context.

An existing connection can be enlisted into the ambient transaction scope: connection.EnlistTransaction(Transaction.Current).

Alternatively, the TS can be created from an existing transaction, e.g. new TransactionScope(transaction), which may or may not be helpful here.

Creating a manual transaction if perfectly fine, but TS (after the gotchas are figured out!) makes dealing with transactions simpler and easier .. at least in most cases :)

Happy coding!


1 The TS isn't being used for "the update statement". It will still [likely] be used for context.SaveChanges() as that will open a new connection which is then auto-enlisted.

I have provided some options above, although I am unsure about the plain "nested" transaction. Seeing the (sealed?) API used in context might reveal more insights as to limitations/restrictions.

  • You are right! When I put the `Open` after the TS the TS is leading. And I would think that in reality this should also happen, because the connection is opened inside the old framework. But it still commits. I'll have to delve a bit deeper into what's going on there and see if one of your suggestions can fix this. Thanks, I'll come back to this. – Gert Arnold Jul 10 '12 at 19:50
  • This brought me on the right track. I've found out that the old framework leaves connections open longer than it should. So it is impossible to use TransactionScope the way I'd like without changing the old code - which we're very reluctant to do. I'm afraid we have to. – Gert Arnold Jul 11 '12 at 10:09