TransactionScope, linq and strange transaction manager issue (HRESULT: 0x8004D024)

Question

I have a service level methods, which make few changes to database and I want them to use transaction control. Such methods can do following: - LINQ SubmitChanges() functionality - Calls to StoredProcedures

Component users can combine set of such elementary operations into something bigger.

I see that there is nice class TransactinScope and trying to use it:

using (TransactionScope transaction = new TransactionScope())
{
     content = repository.CreateBaseContent(content);
     result = repository.CreateTreeRelation(content, parent.Id, name);
     transaction.Complete();
}

public baseContent CreateBaseContent(baseContent content)
{
       EntityContext.baseContents.InsertOnSubmit(content);
       EntityContext.SubmitChanges();

       return content;
}

public CreateTreeRelation (params)
{
// do StoredProcedure call here via LINQ
}

My Assumption was that on outer layers it would be possible to add another level of transaction scope. Instead, I am having following error:

The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)

I am using same (Vista Ultimate) machine for MS SQL 2005 and microsoft development server. From unit tests everything works fine. Same when TransactionScope commented.

I was trying to play with security for DTC (http://support.microsoft.com/kb/899191) and when I set to acccept all inbound and outbound transactions, I have following error message:

Error HRESULT E_FAIL has been returned from a call to a COM component.

During debug, I discovered that in SubmitChanges, Linq Entity Context has Property Transaction IS NULL(!!), and System.Transactions.Transaction.Current has open transaction

Answer 1

Issue happened because Linq Datacontext was created before transactionscope.

Solution was to add own transaction control to LINQ datacontext.

Connection.Open()
Transaction = Connection.BeginTransaction();

and counters to maintain nested calls.

Answer 2

I think you can also use TransactionScope as long as you pass the datacontexts the same connection you .Open.

Another issue you get with TransactionScope is that it doesn't care if the connection string is the same, doing a second .Open will elevate the transaction to a distributed transaction. And then you have to deal with the related configuration, and also the fact that it isn't using the light transaction that is what is needed for that case.