Whats the proper way to do explicit transactions with linq to sql?

Question

I have some scenarios where I need to have multiple calls to .SubmitChanges() on a datacontext, but I want to explicitly control the transaction myself to make it atomic. For a while I have been doing this by creating a connection, creating a transaction on that connection, then creating the datacontext and passing it both. Lets assume for now I dont want to use TransactionScope instead. My code looks like this:

Using conn As New SqlConnection("connection string...")
   conn.Open()
   Using trans = conn.BeginTransaction()
      Dim dc as new DataContext(conn)
      dc.Transaction = trans

      ' do some work 

      trans.Commit()
   End Using
End Using

I began using the Linq To SQL profiler and it breaks this code. For some reason they require you to use the .Connection property on the datacontext to create the transaction. It fails if you use the connection variable directly (which I think is silly). My question is, is it more appropriate to do it this way:

Using conn As New SqlConnection("connection string...")
   conn.Open()
   Dim dc as new DataContext(conn)
   Using trans = dc.Connection.BeginTransaction()
      dc.Transaction = trans

      ' do some work 

      trans.Commit()
   End Using
End Using

Which is the more widely accepted way to do this?

Answer 1

Product prod = db.Products.Single(p => p.ProductID == 15);

if (prod.UnitsInStock > 0)
   prod.UnitsInStock--;

using(TransactionScope ts = new TransactionScope()) {
   db.SubmitChanges();
   ts.Complete();
}

http://msdn.microsoft.com/en-us/library/bb425822.aspx

Answer 2

use TransactionScope

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx http://www.codeproject.com/KB/dotnet/TransactionScope20.aspx

Answer 3

The second snippet doesn't seem appropriate to me. With the second snippet you need to create the transaction after creating the context, which is -at least- from a readability / maintainability perspective less useful. I try to imagine how your code would look when you need to create two DataContext classes, and create the transaction (only) after creating the first context. This makes it pretty hard to keep clean separated code.

I think you should send a mail to Hibernating Rhinos and ask if they fix this bug.