Suppressing TransactionScope enlistment of a readonly EF context?

Question

I have a scenario where I need to open multiple datacontexts which point to different databases. I am only writing to one of the databases though and reading from the others ... so technically the transaction should only be against one of the databases.

I'd like to avoid having the TransactionScope upgrade into a distributed transaction so I don't have to worry about MSDTC ... is there any way to have only one context enlist in the transaction?

Answer 1

I'm not sure if this will enlist the the second connection into the transaction, but you can try to suppress the transactionscope on your select:

using (new TransactionScope(TransactionScopeOption.Suppress))
{}

Answer 2

I have been looking at a similar issue with Linq to Sql - initially our solution is to use the same connection per request. Rick Strahl has done a series of blog posts on this which were worth looking at.

In our solution the DataContext constructor(s) have an overloaded constructor that retrieves the connection from a factory (if no connection exists, the connection passed is stored on the thread)

public DataContext1(connection) : base (ConnectionFactory.GetConnectionFromContext(connection)) {}

This seems to work fine in a WCF scenario, the connection factory can store / retrieve from the ServiceModel.OperationContext.Items collection, and (more importantly) subscribe to the OperationComplete event in order to Close / Dispose the connection on the thread when all operations are done.

I found that we also needed to extend the connection object such that you can prevent automatic closure / diposal of the internal connection when the owning datacontext is disposed (e.g. after each operation scope completes).

I'm looking at the non-WCF scenarios now... TBH it's not pretty. We also don't have the 'OperationCompleted' event trigger that's there in the WCF context.

... any advice on this one would be most welcome!