Using the same SqlConnection for EntityFramework and "normal" SQL calls

Question

I have code with mixed EF and normal SQL calls. The whole thing runs on Azure, so we use ReliableSqlConnection. We are using TransactionScope and we don't have the Distributed Transaction Manager (Azure again). Therefore I have to pass the ReliableSqlConnection to every SQL call.

Now the problem is how to pass the ReliableSqlConnection into a EF call? If found this post:
How to use ADO.net Entity Framework with an existing SqlConnection?

Which would lead to this code:

MetadataWorkspace workspace = new MetadataWorkspace(
  new string[] { "res://*/" },
  new Assembly[] { Assembly.GetExecutingAssembly() });

using (var scope = new TransactionScope())    
using (var conn = DatabaseUtil.GetConnection())
using (EntityConnection entityConnection = new EntityConnection(workspace, (DbConnection)conn))
using (var db = new UniversalModelEntities(entityConnection))
{
    //Do EF things

    //Call other SQL commands

    return db.SaveChanges();
}

But neither can I convert a ReliableSqlConnection to DbConnection, nor does UniversalModelEntities accept a EntityConnection.

Answer 1

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();
            }
        }
     }
}

Answer 2

Have you tried:

using (EntityConnection entityConnection = 
    new EntityConnection(workspace, (DbConnection)conn.Current))