How to implement a single SQL transaction for multiple ObjectContext objects in EF4

Question

I have a fairly large database with tables created for different business modules.

We decided to create different edmx files for different modules, respectively.

However, how can I prevent the use of MSDTC when trying to implement a TransactionScope for a logical action that would entail writing to multiple tables in different edmx? Again, the underlying database is the same; I would not want to use MSDTC for this scenario.

Is there a way to transfer an open SQL connection with an active transaction?

Thanks for the help in advance.

Regards, William

Answer 1

TransactionScope shuts down MSDTC when the databases are different and / or connection strings are different.

Rick Strahl has an excellent article on this (his perspective is LINQ to SQL, but it applies to EF). Money paragraphs:

TransactionScope is a high-level transactional shell that makes it really easy to transfer any code into a transaction without having to manually track transactions. Traditionally, TransactionScope has been .NET. wrappers around the Distributed Transaction Coordinator (DTC), but its functionality has expanded somewhat. One of the problems is that DTC is quite expensive in terms of resource use, and requires that the DTC service is actually running on the machine (another service that is especially tedious when installing the client).

However, recent updates to the TransactionScope and SQL Server client drivers allow you to use the TransactionScope class and ease of use without requiring DTC while you work against a single database and with one consistent row connection. In the above example, since a transaction works with a single instance of a DataContext, the transaction does work without DTC. This is in SQL Server 2008.

See also this SO question / answer where I found a link to Rick's blog.

So, if you connect to the same database and use the same connection string, DTC should not be involved.

Answer 2

Thanks for all the answers above!

by the way, we just managed to find a solution that should explicitly use EntityConnection and EntityTransaction. An example is this:

  string theSqlConnStr = "data source=TheSource;initial catalog=TheCatalog;persist security info=True;user id=TheUserId;password=ThePassword"; EntityConnectionStringBuilder theEntyConnectionBuilder = new EntityConnectionStringBuilder(); theEntyConnectionBuilder.Provider = "System.Data.SqlClient"; theEntyConnectionBuilder.ProviderConnectionString = theConnectionString; theEntyConnectionBuilder.Metadata = @"res://*/"; using (EntityConnection theConnection = new EntityConnection(theEntyConnectionBuilder.ToString())) { theConnection.Open(); theET = null; try { theET = theConnection.BeginTransaction(); DataEntities1 DE1 = new DataEntities1(theConnection); //DE1 do somethings... DataEntities2 DE2 = new DataEntities2(theConnection); //DE2 do somethings... DataEntities3 DE3 = new DataEntities3(theConnection); //DE3 do somethings... theET.Commit(); } catch (Exception ex) { if (theET != null) { theET.Rollback(); } } finally { theConnection.Close(); } } 

with the explicit use of EntityConnection and EntityTransaction, I can achieve the sharing of a single connection and transaction for several ObjectContext objects for the same database, but without the need to use MSDTC.

We hope you find this information helpful. Good luck to you!