Transient Fault Handling with SQL Azure using Entity Framework

Going through what I have so far.

1. The Entity Framework does not provide access to the connection open and section where the SQL is sent to the server, hence it is currently impossibile to provide retry logic around this area.

2. The EF team are aware of this shortfall and are planning on actually integrating retry logic into EF for possibily version 6.

3. As per Case #3 of [1] you can send a SQL command to the database on the OnContextCreated. This however means for EVERY single DB call you make to the DB, you will have to make 2. I wouldn't recommend this in hardly any situation unless you don't care about performance.

4. The only viable option so far is implementing retry logic in the form of the Enterprise Library Transient Fault Handling Application Block [2] around every call you make to the database. In existing applications this is extremely tedious.

5. When I get time I am looking further into the source code of EF to see if anything further can be done, while we wait for EF 6. I would keep an eye on [3]

6. Some hope, it is currently under review by the EF team. [4]

Update: 2013-11-14

Just thought I would update this post to let everyone know that EF6 has been released and supports connection resiliency out of the box. https://www.nuget.org/packages/EntityFramework/

No need for workarounds any more.

Update: 2013-03-23

EF 6 Alpha 3 released with Connection Resiliency - http://entityframework.codeplex.com/wikipage?title=Connection%20Resiliency%20Spec

Update: 2012-11-04

The EF team have officially announced it is planned for EF 6. [4]

[1] http://blogs.msdn.com/b/appfabriccat/archive/2010/12/11/sql-azure-and-entity-framework-connection-fault-handling.aspx

[2] http://msdn.microsoft.com/en-us/library/hh680934(v=pandp.50).aspx

[3] http://entityframework.codeplex.com/wikipage?title=Roadmap

[4] http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/2426525-automatically-perform-retry-logic-for-sql-azure

Thankfully it is pretty simple with the new Transient Fault Handling Application block. Everything you need can be found here:

http://geekswithblogs.net/ScottKlein/archive/2012/01/27/understanding-sql-azure-throttling-and-implementing-retry-logic.aspx

and in video form:

http://channel9.msdn.com/Shows/Cloud+Cover/Episode-68-Throttling-in-SQL-Azure-with-Scott-Klein

An example from the above links:

using (NorthwindEntities dc = new NorthwindEntities())
{
    RetryPolicy myPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3);
    Employee e1 = myPolicy.ExecuteAction<Employee>(() =>
        (from x in dc.Employees
            where x.LastName == "King"
            select x).First());
}

As you can see, all you need to do is create a RetryPolicy and call its ExecuteAction function with a query wrapped in an action.

**EDIT

Example context overrides:

private RetryPolicy m_RetryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(....

public override int SaveChanges()
{
    return m_RetryPolicy.ExecuteAction<int>(() =>
    {
        return base.SaveChanges();
    });
}

// Pass anonymous query func in here
public T AutoRetryQuery<T>(Func<T> query)
{
    return m_RetryPolicy.ExecuteAction<T>(query);
}