Click here to Skip to main content
12,550,348 members (46,701 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


11 bookmarked

Implementing Connection Resiliency with Entity Framework 6

, 24 Oct 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Connection Resiliency with EF 6


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;
                    //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:


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!


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


About the Author

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

You may also be interested in...


Comments and Discussions

QuestionFurther reading Pin
adinas30-Oct-14 23:28
memberadinas30-Oct-14 23:28 
AnswerRe: Further reading Pin
MCY14-Feb-16 22:48
professionalMCY14-Feb-16 22:48 
QuestionGlimpse and tracing Pin
_GP27-Oct-14 20:40
member_GP27-Oct-14 20:40 
QuestionIs it possible to know how many retries have taken place? Pin
Mark Redman9-Aug-14 3:55
memberMark Redman9-Aug-14 3:55 
AnswerRe: Is it possible to know how many retries have taken place? Pin
Pharylon24-Sep-14 9:24
memberPharylon24-Sep-14 9:24 
QuestionCode error? Pin
Niels Peter Gibe11-Apr-14 16:19
memberNiels Peter Gibe11-Apr-14 16:19 
AnswerRe: Code error? Pin
Pharylon12-Apr-14 3:42
memberPharylon12-Apr-14 3:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.161021.1 | Last Updated 24 Oct 2014
Article Copyright 2014 by Pharylon
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid