A few days ago I wrote a blog post about gaining a better understanding of the new connection resiliency feature of EF6. The feature is implemented using an implementation of a new class, IDbExecutionStrategy.

The SQl Server provider that’s bundled with EF6 has a strategy aimed at Windows Azure SQL Database (aka SQL Azure) that is designed to retry a command if a transient connection error is thrown. My blog post listed the transient error messages.

I wanted to see this in action but it’s not simple to make a transient error occur. There was a blog post with sample code for creating a deadlock, but then Glenn Condron on the EF team suggested just throwing the error by leveraging the new EF6 ability to intercept commands & queries headed to the database. It can also intercept data coming back.

A few people have asked how I did this, so I’ll share my setup here.

I played with that a bit and realized that the SqlAzureExecutionStrategy required more than just the correct error code to trigger the retries. It needs a SQLException to throw that error. And a little more banging led me to realize that you can’t just instantiate a SqlException, it has to be done via reflection.

But I don’t give up easily. I found some helpful examples for doing this though it still wasn’t simple. Maybe things have changed, but I finally tweaked teh sample code enough to get what I needed.

Still, I was unsuccessful because EF was first doing it’s initialization tasks and the SqlAzureExecutionStrategy was not responding to errors thrown by those commands. I had to filter those commands out. Then finally, it worked!

There may be an easier way. I know Glenn does this differently. What I have worked out is witnessing the retries but since that’s all I watned to see, I’m not worried about having one of those retries be successful. I know that in the real world, as it’s retrying and the transient connection kicks back in, one of those retries will get through.

So to test out the feature you need four puzzle pieces.

1) Intercept the (non initialization/migration) command

2) Use DbConfiguration to make sure the interception is happening

3) Use DbConfiguration to set up the SqlAzureExecutionStrategy

4) Run an integration test that will attempt one or more queries on the database via EF.

Step 1) Intercept the command and throw the correct error

For this I created a new class, TransientFailureCausingCommandInterceptor, that inherits from IDbCommandInterceptor. There are a number of methods to override. The only one I’m interested in is ReaderExecuting…i.e. a read command is about to execute.

 public class TransientFailureCausingCommandInterceptor : IDbCommandInterceptor
 {
    public void ReaderExecuting(
      DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    etc…

In the command , I filter out commands that are for the database initialization and migration work, in case they are executing.

For any other commands, I write out some info to Debug so I know that a query is about to execute, then I use my helper class to create a fake SQL Exception that throws one of the transient error codes that SqlAzureExecutionStrategy looks for: 10053.

  public void ReaderExecuting(
      DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
      if (!(command.CommandText.Contains("serverproperty") 
        || command.CommandText.Contains("_MigrationHistory")))
      {
        Debug.WriteLine("throwing fake exception from interceptor");
        throw SqlExceptionFaker.Error10053;

      }
    }

The SqlExceptionFaker is the magic for throwing a SqlException. It is a twist on  this example I found from Microsoft MVP, Chris Pietschmann. I found some other ways of doing this but , in my opinion, Chris’ was the best of the solutions that I found.

using System.Collections;
using System.Data.SqlClient;
using System.Runtime.Serialization;

namespace SqlExceptions
{
  public static class SqlExceptionFaker
  {
    private static SqlException _error10053;

    public static SqlException Error10053
    {
      get
      {
        if (_error10053 == null)
        {
          _error10053 = Generate(SqlExceptionNumber.TransportLevelReceiving);
        }
        return _error10053;
      }

    }
    public enum SqlExceptionNumber : int
    {
      TimeoutExpired = -2,
      EncryptionNotSupported = 20, 
      LoginError = 64,
      ConnectionInitialization = 233,
      TransportLevelReceiving = 10053,
      TransportLevelSending = 10054, 
      EstablishingConnection = 10060,
      ProcessingRequest = 40143, 
      ServiceBusy = 40501, 
      DatabaseOrServerNotAvailable = 40613
    }

    public static SqlException Generate(SqlExceptionNumber errorNumber)
    {
      return SqlExceptionFaker.Generate((int) errorNumber);
    }

    public static SqlException Generate(int errorNumber)
    {
      var ex = (SqlException) FormatterServices.GetUninitializedObject(typeof (SqlException));

      var errors = GenerateSqlErrorCollection(errorNumber);
      SetPrivateFieldValue(ex, "_errors", errors);
      return ex;
    }

    private static SqlErrorCollection GenerateSqlErrorCollection(int errorNumber)
    {
      var t = typeof (SqlErrorCollection);
      var col = (SqlErrorCollection) FormatterServices.GetUninitializedObject(t);
      SetPrivateFieldValue(col, "errors", new ArrayList());
      var sqlError = GenerateSqlError(errorNumber);
      var method = t.GetMethod(
        "Add",
        System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance );
      method.Invoke(col, new object[] {sqlError});
      return col;
    }

    private static SqlError GenerateSqlError(int errorNumber)
    {
      var sqlError = (SqlError) FormatterServices.GetUninitializedObject(typeof (SqlError));

      SetPrivateFieldValue(sqlError, "number", errorNumber);
      SetPrivateFieldValue(sqlError, "message", errorNumber.ToString());
      SetPrivateFieldValue(sqlError, "procedure", string.Empty);
      SetPrivateFieldValue(sqlError, "server", string.Empty);
      SetPrivateFieldValue(sqlError, "source", string.Empty);
      SetPrivateFieldValue(sqlError, "win32ErrorCode", errorNumber);

      return sqlError;
    }

    private static void SetPrivateFieldValue(object obj, string field, object val)
    {
      var member = obj.GetType().GetField(
        field,
        System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance
        );
      member.SetValue(obj, val);
    }
  }
}

Phew!

Step 2) Force the context to use this interceptor

This happens in the lovely new DbConfiguration class. Read more about it here.

public class CustomDbConfiguration : DbConfiguration
{
  public CustomDbConfiguration()
  {
   AddInterceptor(new CasinoModel.TransientFailureCausingCommandInterceptor());
   }
}

Step 3) Make sure your DbConfiguraiton class is wired up in app/web.config in the EntityConnection section:

 <entityFramework codeConfigurationType="DataLayer.DbConfigurations.CustomDbConfiguration,CasinoModel">
  . . .

 </entityFramework>

Step 4) Test

This is a test that works with my model. Notice that my test specifies a connection string. That ensures that my context is using the SQL Azure connection I set up in my config file.

   [TestMethod, TestCategory("Connection Resiliency")]
    public void CanHitSqlAzureDbWithTransientFailure()
    {
      int slotcount = 0;
      using (var context = new CasinoSlotsModel(connectionStringName: "CasinoHotelsAzure"))
      {
        foreach (var casino in context.Casinos)
        {
          context.Entry(casino).Collection(c => c.SlotMachines).Load();
          slotcount += casino.SlotMachines.Count;
        }
      }
      Assert.AreNotEqual(0, slotcount);
    }

The test fails because an exception was thrown – the SqlException that I faked. That’s because an error is getting thrown. When I check the output

The SqlException caused a series of exceptions in response. Notice the nice message from EntityException: “consider using a SqlAzureExecutionStrategy”.

Result Message:
Test method AutomatedTests.UnitTest1.CanHitSqlAzureDbWithTransientFailure threw exception:

System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details. —> System.Data.Entity.Core.EntityException: An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy. —>

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. —>

System.Data.SqlClient.SqlException: Exception of type ‘System.Data.SqlClient.SqlException’ was thrown.

Looking at the failed test’s OUTPUT I see the text spit out by the ReaderExecuting method:

The method was only hit once, so I see my message only once.

Step 5) Add in the SqlAzureConnectionStretegy to the DbConfiguration file:

 public CustomDbConfiguration()
  {

    AddInterceptor(new CasinoModel.TransientFailureCausingCommandInterceptor());
    SetExecutionStrategy
      (SqlProviderServices.ProviderInvariantName, () => new SqlAzureExecutionStrategy());
   }

Using the default settings of SqlAzureExecutionStrategy will cause 5 retries.

Step 6) Run the test again:

The test hangs for a lot longer than fails. Why longer? The retries! I get 6 messages. The first is the initial problem and the other 5 are for each of the 5 retries.

Why does it still fail? Because my setup doesn’t “turn off” the error. That’s fine. I just want to see that it does actually retry.

The exception is different this time. RetryLimitExceededException, max retries (5) , etc…

Result Message:
Test method AutomatedTests.UnitTest1.CanHitSqlAzureDbWithTransientFailure threw exception:

System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details. —> System.Data.Entity.Infrastructure.RetryLimitExceededException: Maximum number of retries (5) exceeded while executing database operations with ‘SqlAzureExecutionStrategy’. See inner exception for the most recent failure. —>

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. —>

System.Data.SqlClient.SqlException: Exception of type ‘System.Data.SqlClient.SqlException’ was thrown.

Step 7) Modify the connection retries

Next I’ll change the default of the SqlAzureExecutionStrategy to retry only 3 times.

(SqlProviderServices.ProviderInvariantName, () => new SqlAzureExecutionStrategy(3,new TimeSpan(15)));

Look at the output after I run the test again. Only 3 retries then it gives up.

So that’s it. I’m content to see this feature in action.

I was also happy to see a comment in the previous blog post from a developer who has seen the benefits of this feature already in their production environment.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!