Retry Policy for LINQ to SQL Stored Procedure calls

Question

I know this has been asked several times before, but I thought I may have a different solution; however, I need some help to get it to work.

The idea: Business Layer calls a Data layer function. The data layer function wraps the call to the database context's stored procedure function in a retry policy. Essentially, I want the LINQ tool to import and manage the actual call to the stored procedure, but I wish it would wrap the logic with some retry policy for retry-able errors.

Much of this concept is taken from What is good C# coding style for catching SQLException and retrying, however, this appears to only work for LINQ to SQL commands, not calling stored procedure functions generated in the DBML.

Old method:

Sub BLFunctionWithoutRetry()
    Using DB as CustDataContext
        DB.sp_GetCustomers()
    End Using
End Sub

New Logic with Retry:

Sub BLFunctionWithRetry()
    GetCustomers()
End Sub

Function GetCustomers() As List(Of Customer)
    Return Retry(Of List(Of Customer))(sp_GetCustomers())
End Function

Function Retry(Of T)(FunctionToCall As Func(Of T)) As T
    Dim Attempt As Integer = 0
    While True
        Try
            Using DB as MyDataContext
                DB.FunctionToCall()
            End Using
        Catch ex as SQLException
            If Retryable() Then Attempt += 1
            If Attempt >= Max Or Not Retryable() Then Throw
        End Try
    End While

Function Retryable() As Boolean
    Return True
End Function

This is the general idea; however, I need help writing the Retry function above. I am getting the obvious error FunctionToCall() is not a member of 'MyDataContext'. Additaionlly, I dont know how to write this so it will work for any stored procedure with any length of input parameters.

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). It's a standard .NET library, so it should be usable from VB as well (though my examples below are C# - forgive me for not knowing VB all that well).

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.

Answer 2

Some times the answer is in the question, quite literally in this case. The function Retry in What is good C# coding style for catching SQLException and retrying actually works for stored procedure calls too! For those still working in VB, here is the code:

Public Class DatabaseHelper

    Private Enum RetryableSqlErrors
        SqlConnectionBroken = -1
        SqlTimeout = -2
        SqlOutOfMemory = 701
        SqlOutOfLocks = 1204
        SqlDeadlockVictim = 1205
        SqlLockRequestTimeout = 1222
        SqlTimeoutWaitingForMemoryResource = 8645
        SqlLowMemoryCondition = 8651
        SqlWordbreakerTimeout = 30053
    End Enum


    Public Shared Sub Retry(Of T As {DataContext, New})(retryAction As Action(Of T), ByVal MaxAttempts As Integer, ByVal Delay As Integer)
        Dim retryCount = 0
        Using ctx = New T()
            While True
                Try
                    retryAction(ctx)
                    Exit Sub
                Catch ex As SqlException
                    If Not [Enum].IsDefined(GetType(RetryableSqlErrors), ex.Number) Then
                        Throw
                    End If

                    retryCount += 1
                    If retryCount > MaxAttempts Then
                        Throw
                    End If

                    Thread.Sleep(If(ex.Number = CInt(RetryableSqlErrors.SqlTimeout), Delay * 5, Delay))
                End Try
            End While
        End Using
    End Sub

    Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T
        target = value
        Return value
    End Function
End Class

Then to call a stored proc:

Dim results = New YourType
Retry(Of YourDataContext)(Function(ctx) InlineAssignHelper(Of YourType)(results, ctx.YourStoredProc("Your", "Proc", "Inputs", 1).First), 3, .5)
Return results

Slight change from the original: I decided to use only 1 delay value, and multiplied it by 5 for a time out.

Thank you to David Clarke for the original post! Very impressive function.