Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Ignore SqlTransaction.Commit within TransactionScope

Tags:

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.