Linq to SQL TransactionScope

Question

I have the following script:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },EnterpriseServicesInteropOption.Automatic)) { using (DataContext db = new DataContext()) { db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { bool outcome = InvokeInTransaction<string, object>(inputDict, out outputDict); db.Transaction.Commit(); } catch (Exception ex) { response.Outcome = BusinessEntityResponse.SystemError; db.Transaction.Rollback(); } } } 

Inside the InvokeInTransaction call, there are a number of calls made to the LTS repository to perform various data changes. The problem is that inside the repository there is still

 using (var db = new DataContext()) 

Inside is a save code. Checking the context in the repository shows Transaction = null, and I suspect that the "internal" context is not aware of the Ambient transaction. It can be done? I understand that EF manages this under the covers, and the limitation is that the repository code cannot be changed. Any help?

Answer 1

We use LinqToSql and TransactionScope for several database transactions. You really have to control your connection / context / transaction life cycles if you are going to try it.

• We control DataContext instances by the rule: if you are a beginner, you do this using the using statement.
• We manage the connection life cycles by the rule: if you open it, you should close it (but, as a rule, DataContext instances allow this).
• We manage the transaction life cycles by the rule: let the DataContext control what happens in SubmitChanges and let TransactionScope control what happens inside its usage block.

Here is a sample code:

 using (OuterDataContext outerDataContext = GetOuterDataContext()) { using (InnerDataContext innerDataContext = GetInnerDataContext()) { try { OuterRepository outerRepository = new OuterRepository(); // may read records into memory for updating/deleting. outerRepository.WorkWithOuterRecords(outerRecords, outerDataContext); InnerRepository innerRepository = new InnerRepository(); // may read records into memory for updating/deleting. innerRepository.WorkWithInnerRecords(innerRecords, innerDataContext); using (TransactionScope scope = new TransactionScope()) { //starts a local tranaction in outerDB, held open by scope outerDataContext.SubmitChanges(); //promotes the transaction to distributed, still held open by scope innerDataContext.SubmitChanges(); // and done scope.Complete(); } } catch (Exception ex) { LoggerClient.Log(ex); response.Message = "It didn't save anything."; } } }