Click here to Skip to main content
Click here to Skip to main content

Implementing Connection Resiliency with Entity Framework 6

, 11 Apr 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Connection Resiliency with EF 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, add them to the list above.

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pharylon
Software Developer (Junior) n/a
United States United States
No Biography provided

Comments and Discussions

 
QuestionIs it possible to know how many retries have taken place? PinmemberMark Redman9-Aug-14 3:55 
AnswerRe: Is it possible to know how many retries have taken place? PinmemberPharylon24-Sep-14 9:24 
QuestionCode error? PinmemberNiels Peter Gibe11-Apr-14 16:19 
AnswerRe: Code error? PinmemberPharylon12-Apr-14 3:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.1 | Last Updated 11 Apr 2014
Article Copyright 2014 by Pharylon
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid