by Njål

Windows Azure SQL – Entity Framework 6 Retry Logic

 

One of the biggest drawbacks of using Azure SQL is that the connection towards the database is a lot more fragile and unstable than when you’re using a database server on your LAN. Also – your Azure database is running on servers with several hundred other databases – so the database server might disconnect you at any given time to ensure fairness of service etc.

These are transient errors – meaning that a retry of the query most often will succeed and fix the error.

Microsoft has released several code blocks and guidelines to help developers use this retry logic – http://code.msdn.microsoft.com/windowsazure/SQL-Azure-Retry-Logic-2d0a8401

And luckily – the EntityFramework team has added support for this in version 6 as well.

Here’s how you can get started with EF6 (beta 1) – using the built in SqlAzureExecutionStrategy

 

1. Install EF6 beta by opening the NUGET console in Visual Studio and typing
     PM> Install-Package EntityFramework –Pre

2. Install the EF6 Template generator – http://visualstudiogallery.msdn.microsoft.com/18a7db90-6705-4d19-9dd1-0a6c23d0751f. This will create the C# code for you – which will handle the communication towards the database

3. Restart Visual Studio

4. Add New Item  -> EDMX file. Connect to Database and/or specify your datamodel. Just like older versions of Entity Framework.

5. Right click in the EDMX editor – and select Add Code Generation Item. Select EF 6.x DbContext Generator.

6.
Compile. You should now have EF6 up and running. Only one crucial part missing – the Azure Retry Logic.

7. Add a class inside your Project with the following code. It will get picked up upon Application Start – and make sure that all the EF Database queries will be retried if they fail.

 

using System;
using System.Data.Entity.Config;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServer;

namespace Www.Business.Database
{
    public class SqlAzureDbConfiguration : DbConfiguration
    {
        public SqlAzureDbConfiguration()
        {
            AddExecutionStrategy(() => new SqlAzureExecutionStrategy(), null);
        }

        protected void AddExecutionStrategy(Func<IExecutionStrategy> getExecutionStrategy, string serverName)
        {
            AddDependencyResolver(new ExecutionStrategyResolver<IExecutionStrategy>("SqlAzureExecutionStrategy", serverName, getExecutionStrategy));
        }
    }
}

(The example provided here: http://entityframework.codeplex.com/wikipage?title=Connection%20Resiliency%20Spec does not work/compile)