none
Linq to SQL and TransactionScope RRS feed

  • Question

  • Hello,

     

    I am trying to use TransactionScope with Linq to SQL and I am having problem when I have nested scopes.  If I have an outer scope that with TransactionScopeOption.Required and an inner scope with TransactionScopeOption.Suppress and I throw an exception after the inner scope completes and before I call outerScope.Complete(), the work that I do inside the innerScope gets roll back alone with the work done in the outerScope.  See test code below:

     

    [Test]

    [ExpectedException(typeof(Exception), "TEST")]

    public void TransactionScopeAndLinq()

    {

    UserLoginContext db = new UserLoginContext();

    string userName = "Test-[" + DateTime.Now.Ticks + "]";

    try

    {

    Assert.IsNull(Transaction.Current);

    using (TransactionScope outerScope = new TransactionScope(TransactionScopeOption.Required))

    {

    Assert.IsNotNull(Transaction.Current);

    UserLogin outter = new UserLogin();

    outter.UserName = "A";

    outter.Success = false;

    db.GetTable<UserLogin>().InsertOnSubmit(outter);

    db.SubmitChanges();

    using (TransactionScope innerScope = new TransactionScope(TransactionScopeOption.Suppress))

    {

    Assert.IsNull(Transaction.Current);

    UserLogin inner = new UserLogin();

    inner.UserName = userName;

    inner.Success = true;

    db.GetTable<UserLogin>().InsertOnSubmit(inner);

    db.SubmitChanges();

    innerScope.Complete();

    }

    Assert.IsNotNull(Transaction.Current);

    throw new Exception("TEST");

    outerScope.Complete();

    }

    }

    catch (Exception exception)

    {

    UserLogin userLogin = (from ul in db.UserLogins

    where ul.UserName == userName

    select ul).FirstOrDefault<UserLogin>();

    Assert.IsNotNull(userLogin, "UserLogin object was not persisted to Database.");

    throw exception;

    }

    }

     

    So object named "inner" is not persisted to database but I expected to be persisted since I am using TransactionScopeOption.Suppress in the inner scope.  Can you please explain why I am getting this behavior, am I missing something? Please help me!

    Thanks,

     

    -Agustin

    Wednesday, July 2, 2008 3:18 PM

Answers

  • The reason for this behavior is that you are using the same DataContext (and therefore the same SQL connection) in the nested scope.

     

    When the outer transaction is rolled back, changes performed against that connection are undone - therefore changes in the nested scope are not committed either.

     

    Thanks,

     

    --Samir

     

    Thursday, July 3, 2008 6:45 PM

All replies

  • The reason for this behavior is that you are using the same DataContext (and therefore the same SQL connection) in the nested scope.

     

    When the outer transaction is rolled back, changes performed against that connection are undone - therefore changes in the nested scope are not committed either.

     

    Thanks,

     

    --Samir

     

    Thursday, July 3, 2008 6:45 PM
  • Samir, thanks for your answer/explanation!

    Friday, July 4, 2008 1:30 AM