Multiple connection strings an transactions

Jun 5, 2013 at 7:45 AM
Good day.

We now have multiple models, and each has it's own connection string entry in the config. However, they all connect to the same database.
I would like to get these various DbContexts to all use a single underlying connection object so that any changes made across the models will be wrapped in a single transaction without requiring to enlist in MSDTC.
Do you have any guidance for me on how to achieve this?
I know I can modify the my context.tt template to generate a second constructor that takes in a DbConnection and then I can pass that through to the base constructor of DbContext. This is fine, and what I will probably do for my custom implementation of IDomainObjectContainer.
However, I am not clear how to gain this control when running the NO Framework.

Thank you much.
Coordinator
Jun 5, 2013 at 8:57 AM
If you are using Code First (which I assume you now are from your reference to context.tt), then you can initiate the context with the DbConnection in the EntityPersistor i.e.:
p.UsingCodeFirstContext(() => new MyContext([additional stuff here]));
whether that will achieve a single transaction across contexts without enlisting DTS I am not sure: but I suggest you do some experimentation and validate that for yourself.
Jun 5, 2013 at 9:56 AM
Hi there. I think that might work, except that we are not using code first.
It is possible to generate a context off a edmx using a template also.
So, is there a similar hook when working with an edmx (not code first)?
Coordinator
Jun 5, 2013 at 10:34 AM
If you are generating multiple .edmx files from the same database then it is possible that EF is smart enough to figure out that the various calls are going to the same database and enlist them in a single transaction. I have a vague feeling that Dan once told me he thought this was the case - but I absolutely can't vouch for it. You need to verify this yourself. I don't think that doing anything to the Context will help at all.

And if EF is not smart enough to figure that out then I'm not sure the Code First solution would work either.

But either way, if EF can't figure it out n Naked Objects will look after the transactionality via DTS anyway.
Jun 5, 2013 at 10:39 AM
I'll try to verify some time whether EF is clever enough to do this automatically.
I don't think that doing anything to the Context will help at all.
Well, if you new them up passing the same connection object in, that would help I think.
Jun 5, 2013 at 10:40 AM
But either way, if EF can't figure it out n Naked Objects will look after the transactionality via DTS anyway.
Using TransactionScope?
Editor
Jun 5, 2013 at 10:46 AM
I remember researching this (and I think Stef might've chipped in as well); my recollection is that NO uses a TransactionScope, but that so long as all the connections have the same connection string, then there will only be a single local transaction.

If the connection strings are different across your clusters, then it'll be a distributed transaction.

ie, it should "just work".
Jun 5, 2013 at 10:49 AM
Ok, that would be beautiful and what I was hoping for.
Editor
Jun 5, 2013 at 10:51 AM
It is what we see with code-first, at any rate.

(We gave up on model-first because those .edmx files aren't really mergeable in multi-team environments. This might be another factor for you to consider).
Jun 5, 2013 at 11:00 AM
MSDN - TransactionScope Improvements
In version 2.0 of the .NET Framework and SQL Server 2005, opening a second connection inside a TransactionScope would automatically promote the transaction to a full distributed transaction, even if both connections were using identical connection strings. In this case, a distributed transaction adds unnecessary overhead that decreases performance.

Starting with SQL Server 2008 and version 3.5 of the .NET Framework, local transactions are no longer promoted to distributed transactions if another connection is opened in the transaction after the previous transaction is closed. This requires no changes to your code if you are already using connection pooling and enlisting in transactions. The following sections describe the situation in more detail.
Editor
Jun 5, 2013 at 11:04 AM
w00t!
Jun 5, 2013 at 11:07 AM
Edited Jun 5, 2013 at 11:07 AM
(We gave up on model-first because those .edmx files aren't really mergeable in multi-team environments. This might be another factor for you to consider).
Yup, the edmx merging issue is something that we have to be careful about.
However, as a team we seem to still prefer that over code first. And we don't have time to convert everything now.
Feb 14, 2014 at 12:40 PM
Just a know to other people doing this, the connection strings have to be identical, including text casing, else it is promoted to a distributed transaction.
Would have been better if the EF team did not compare the string, but the resulting connection properties after parsing.