Entity Framework Command Intercept change context connection string

Question

Goal:

fall back to the backup database when the main one is not reachable / down.

Current implementation:

when issue is detected, use class implementing IDbCommandInterceptor to change connection string to fallback server.

1. Close connection on the intercepted context.
2. Change connectionstring.
3. Open connection.

This works fine with ReaderExecuting, but doesn't with NonQueryExecuting. I can see that connection to the fallback server is open and looks fine, but I get the following during context.SaveChanges():

The underlying provider failed on Commit.

InnerException: Value cannot be null. Parameter name: connection

UPDATE: After looking a bit more into this I see that the issue happens within the transaction which is created by SaveChanges(). Closing connection on context results in throwing exception by the transaction object on commit. I couldn't find the way to change the connection or connection string for this transaction. Disposing it at the ConnectionGetting() using IDbTransactionInterceptor and creating new transaction on context didn't help , it would still proceed to trying to commit (probably a race condition and not a good way to do this anyway). ChangeDatabase() wouldn't seem to work as the connection should be to the same server.

So the question is now: is there a way to change connection / connection string for an existing transaction?

StackTrace:

at System.Data.Entity.Utilities.Check.NotNull[T](T value, String parameterName)
at System.Data.Entity.Infrastructure.Interception.DbTransactionInterceptionContext.WithConnection(DbConnection connection)
at System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.Commit(DbTransaction transaction, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.EntityClient.EntityTransaction.Commit()

Answer 1

The correct place to change the connection string is in implementing Opening() of IDbConnectionInterceptor interface. This works for simple reads, writes and transactions. Implementing IDbCommandInterceptor members is useful for the complete solution while dealing with individual sql command behavior.