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 { public PharylonExecutionStrategy() { } 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 , -2, 2601 }; if (sqlException.Errors.Cast<SqlError>().Any(x = > errorsToRetry.Contains(x.Number))) { retry = true ; } else { } } 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!