tugberkugurlu/GenericRepository — pull request #5 by bmccord (2012-04-25 21:46:18+00:00)
Title: SQL Server 2005 and TransactionScope Compatibility

While working with SQL 2005, I noticed that even if you aren't doing a distributed transaction (across servers), every time you start a new context, a new connection is opened and SQL Server 2005 automatically promotes the transaction to be a distributed transaction. Unless you have the DTC setup exactly right on 2005, you will run into issues when you try to commit the transaction.

To fix this, each repository that is participating in the transaction needs to run off of the same context and (unfortunately), you must open the connection yourself.

I decided to make the context property public rather than create a "EnlistInContext" method that could set the context member to the correct one just so that I could avoid a dependency on EF in the interface and also to avoid changing the interface to also accept a C (context) generic. Changing the property from protected to public was the path of least resistance and once changed, allows code like this:

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
PipelineImportContext context = new PipelineImportContext();

newDodgeItemRepository.Context = context;
dodgePreproductionRepository.Context = context;
hotelRepository.Context = context;
chainRepository.Context = context;
projectRepository.Context = context;
commentRepository.Context = context;
projectAmenityRepository.Context = context;

((IObjectContextAdapter)context).ObjectContext.Connection.Open(); // You must open the connection

// Do updates, deletes, etc... Don't forget to call .Save() as appropriate for each repository

scope.Complete();
}

The strange open syntax is a requirement of EF...I think they are supposed to fix this later.

When testing with 2008, none of this strangeness was required. I could simply open the TransactionScope, use each repository as a wanted (without creating a separate context, setting it for each repo, and opening the connection) and complete the scope at the end. But, I thought I would go ahead and document the problem and make the change for those who may be stuck on 2005.

Hope it helps.