Retry Logic for LINQ-TO-SQL on SQL Azure - Efficient implementations?

Question

I have a fairly large web application using LINQ-TO-SQL running in Azure, and I'm experiencing Transient errors from SQL-Azure and therefore need to implement retries. I'm aware of the Transient Fault Handling Framework and several sites that give examples how to use it, but it looks like you have to wrap every one of your LINQ queries in something similar to this:

RetryPolicy retry = new RetryPolicy<MyRetryStrategy>(5, TimeSpan.FromSeconds(5));
Result = retry.ExecuteAction(() =>
{
   … LINQ query here...
});

With hundreds of LINQ queries in my data layer, this seems really messy, plus the fact that a lot of times the query isn't actually executed until the results are enumerated. For example, most of my functions in my data layer return an IQueryable<> up to the business layer, (which makes them more flexible than returning a List). So that would mean you have to litter your business logic layer with database retry logic - ugly.

So I guess in order to keep the retry logic in the data layer, I would have to put .ToList()'s on all my queries so they are executed right there, and not in the layer above.

I really wish there was a way to implement retry logic in some base class and not have to change all of my queries. Seems like EF would have this problem too.

Is the real answer to try and talk the SQL-Azure team to do the auto-retries, so we dont have to worry about that in our code?

Answer 1

After needing to implement something like this, I went ahead and made it a library: https://github.com/daveaglick/LinqToSqlRetry (MIT licensed and available on NuGet).

You can retry SubmitChanges() calls by writing SubmitChangesRetry() instead:

using(var context = new MyDbContext())
{
  context.Items.InsertOnSubmit(new Item { Name = "ABC" });
  context.SubmitChangesRetry();
}

You can also retry queries by using the Retry() extension method:

using(var context = new MyDbContext())
{
  int count = context.Items.Where(x => x.Name == "ABC").Retry().Count();
}

The specific retry logic is controllable by policies. Under the hood, the retry mechanism looks like:

int retryCount = 0;
while (true)
{
    try
    {
        return func();
    }
    catch (Exception ex)
    {
        TimeSpan? interval = retryPolicy.ShouldRetry(retryCount, ex);
        if (!interval.HasValue)
        {
            throw;
        }
        Thread.Sleep(interval.Value);
    }
    retryCount++;
}

Understand that the function in the call to func() and the retryPolicy object are provided based on usage. This just gives you an idea what's going on during the retry loop. Just look in the repository for more information.