Implementing Connection Resiliency with Entity Framework 6

Introduction

In Entity Framework 6, we got a new way to deal with timeouts, deadlocks, and other transient SQL errors. Microsoft calls this "connection resiliency," and I'm going to show you how to implement it. Fortunately, it's really easy!

One quick note before we start: this is all written with the assumption of using Microsoft SQL Server (which I think most people are) and C#. But, if you use EF to connect to MySQL or use Visual Basic, you should be able to adapt this to your needs.

Implementing Connection Resiliency

First, we need to create a class that inherits from DbExecutionStrategy and overrides ShouldRetryOn(). Here is mine, in full:

public class PharylonExecutionStrategy : DbExecutionStrategy
    {
        /// <summary>
        /// The default retry limit is 5, which means that the total amount of time spent 
        /// between retries is 26 seconds plus the random factor.
        /// </summary>
        public PharylonExecutionStrategy()
        {
        }

        /// <summary>
        /// Creates a new instance of "PharylonExecutionStrategy" with the specified limits for
        /// number of retries and the delay between retries.
        /// </summary>
        /// <param name="maxRetryCount"> The maximum number of retry attempts. </param>
        /// <param name="maxDelay"> The maximum delay in milliseconds between retries. </param>
        public PharylonExecutionStrategy(int maxRetryCount, TimeSpan maxDelay)
            : base(maxRetryCount, maxDelay)
        {
        }

        protected override bool ShouldRetryOn(Exception ex)
        {
            bool retry = false;

            SqlException sqlException = ex as SqlException;
            if (sqlException != null)
            {
                int[] errorsToRetry =
                {
                    1205,  //Deadlock
                    -2,    //Timeout
                    2601  //primary key violation. Normally you wouldn't want to retry these, 
                          //but some procs in my database can cause it, because it's a crappy 
                          //legacy junkpile.
                };
                if (sqlException.Errors.Cast<SqlError>().Any(x => errorsToRetry.Contains(x.Number)))
                {
                    retry = true;
                }
                else
                {
                    //Add some error logging on this line for errors we aren't retrying.
                    //Make sure you record the Number property of sqlError. 
                    //If you see an error pop up that you want to retry, you can look in 
                    //your log and add that number to the list above.
                }
            }
            if (ex is TimeoutException)
            {
                retry = true;
            }
            return retry;
        }
    } 

ShouldRetryOn is a pretty simple method. It takes an Exception as an argument, examines it, and returns a boolean indicating to the EF context whether it should be retried after a short wait.

Most error codes are going to be returned by SQL itself. These are the ones my server uses, which is running SQL Server 2005 (yeah, I know...). If you're using SQL Server, you can get yours by running this on the "master" table:

SELECT * FROM SYSMESSAGES  

But the best thing you can do is log any SQLExceptions it doesn't catch, record the error numbers somewhere, and if you see the same ones over and over (and it's the kind that make sense to retry on) add them to the list.

The base class will retry 5 times, with the wait time between attempts increasing exponentially (roughly, it retires once immediately, then if it fails again, waits about a second before retrying, then waits about 7 seconds, etc.). If you wish to change the amount of wait time before retries, you can also override the method GetNextDelay(). Doing so is not shown here.

Now, we just need to hook it into our Entity Framework. That couldn't be any easier. We just need to create a new class that inherits from DbConfiguration. Here's mine, in full:

class PharylonConfiguration : DbConfiguration
   {
       public PharylonConfiguration()
       {
           SetExecutionStrategy("System.Data.SqlClient", () => new PharylonExecutionStrategy());
       }
   }

This class just needs to be somewhere in the assembly. The context will automatically find it on compilation (it's like magic, seriously).

And that's it! We now have a resilient Entity Framework connection!