Problem is ReliableSqlConnection implements IDbConnection interface, but EF context constructors all accept DbConnection (not interface). I don't know why they made such decision, maybe they have a valid reasoning behind it, maybe it's just bad design decision. However, you have to live with that. Note that using whatever is returned by ReliableSqlConnection.Open() or ReliableSqlConnection.Current is not an option - it will work yes, but you will just use regular connection then without retrying logic, basically bypassing whole ReliableSqlConnection class purpose. Instead you might try to create a wrapper around ReliableSqlConnection, like this:
public class ReliableSqlConnectionWrapper : DbConnection {
private readonly ReliableSqlConnection _connection;
public ReliableSqlConnectionWrapper(ReliableSqlConnection connection) {
_connection = connection;
}
protected override DbTransaction BeginDbTransaction(System.Data.IsolationLevel isolationLevel) {
return (DbTransaction) _connection.BeginTransaction();
}
public override void Close() {
_connection.Close();
}
public override void ChangeDatabase(string databaseName) {
_connection.ChangeDatabase(databaseName);
}
public override void Open() {
_connection.Open();
}
public override string ConnectionString
{
get { return _connection.ConnectionString; }
set { _connection.ConnectionString = value; }
}
public override string Database
{
get { return _connection.Database; }
}
public override ConnectionState State
{
get { return _connection.State; }
}
public override string DataSource
{
get { return _connection.Current?.DataSource; }
}
public override string ServerVersion
{
get { return _connection.Current?.ServerVersion; }
}
protected override DbCommand CreateDbCommand() {
return _connection.CreateCommand();
}
protected override DbProviderFactory DbProviderFactory {
get { return SqlClientFactory.Instance; }
}
}
Here we inherit from DbConnection as EF wants, and forward all logic to the underlying ReliableSqlConnection instance. Note that you might need to override more methods from DbConnection (like Dispose) - here I just show how to override only required (abstract) members.
Alternative option to the wrapper would be copy the source code of ReliableSqlConnection class and modify it to inherit DbConnection.
Then, in your EF context you need to add a constructor which accepts DbConnection:
public UniversalModelEntities(DbConnection connection, bool contextOwnsConnection) : base(connection, contextOwnsConnection) {}
Which just calls base class constructor with the same parameters. Second parameter (contextOwnsConnection) defines if context is capable to manage this connection, for example to close it when context is disposed.
If you use EF database first approach - edit EF template which generates code for your context and add this constructor there.
After all this, you can do:
using (var scope = new TransactionScope()) {
using (var conn = new ReliableSqlConnection("")) {
using (var ctx = new UniversalModelEntities(new ReliableSqlConnectionWrapper(conn), false)) {
}
}
}
After some investigation I came to a conclusion that approach above might be hard to implement, because connection wrappers are not quite compatible with entity framework. Consider more simple alternative - use DbCommandInterceptor and reuse retry logic with extension methods provided by the same Transient Fault Handler library which provides your ReliableSqlConnection:
public class RetryInterceptor : DbCommandInterceptor {
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
interceptionContext.Result = ((SqlCommand)command).ExecuteNonQueryWithRetry();
}
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {
interceptionContext.Result = ((SqlCommand)command).ExecuteReaderWithRetry();
}
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
interceptionContext.Result = ((SqlCommand)command).ExecuteScalarWithRetry();
}
}
So we intercept commands and forward their execution to Transient Fault Handler block. Then in main method:
static void Main() {
// don't forget to add interceptor
DbInterception.Add(new RetryInterceptor());
MetadataWorkspace workspace = new MetadataWorkspace(
new string[] {"res://*/"},
new[] {Assembly.GetExecutingAssembly()});
// for example
var strategy = new FixedInterval("fixed", 10, TimeSpan.FromSeconds(3));
var manager = new RetryManager(new[] {strategy}, "fixed");
RetryManager.SetDefault(manager);
using (var scope = new TransactionScope()) {
using (var conn = new ReliableSqlConnection("data source=(LocalDb)\\v11.0;initial catalog=TestDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework")) {
// pass Current - we don't need retry logic from ReliableSqlConnection any more
using (var ctx = new TestDBEntities(new EntityConnection(workspace, conn.Current), false)) {
// some sample code I used for testing
var code = new Code();
code.Name = "some code";
ctx.Codes.Add(code);
ctx.SaveChanges();
scope.Complete();
}
}
}
}
