Retry LINQ to SQL query on transient error

It’s convenient to override the System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) method so that it retries in the event of transient errors, such as deadlocks or timeouts, when inserting, updating or deleting records.

My question is, is there an analogous function that can be overridden to handle such errors when executing a LINQ to SQL query? I’m guessing/hoping there would be a method in the DataContext class that does the actual call to the database and that could be overridden to perform a retry.

The examples I’ve seen (such as shown below) typically wrap the LINQ expression and th开发者_如何学编程e method call that enumerates it in a retry block:

try
{
    e.Result = retry.ExecuteAction(() =>
        {
            Deadlock(); // Artificially create a deadlock condition

            CustomerOrdersDataContext ctx = new CustomerOrdersDataContext();
            ctx.Connection.ConnectionString = builder.ConnectionString;
            ctx.CommandTimeout = 3;

            var results = from c in ctx.customers
                            from o in c.orders
                            from i in o.order_items
                            select new { c.lname, c.fname, i.product.product_name, i.quantity };

            return results.ToList();
        });
}
catch (SqlException ex)
{
    MessageBox.Show(ex.Message, "SqlException");
}

}

(from http://social.technet.microsoft.com/wiki/contents/articles/retry-logic-for-transient-failures-in-sql-azure.aspx)

I’m hoping to avoid having to do this every time a LINQ expression is enumerated. Furthermore, with delayed loading, the actual call to the database may be well separated in time and code from the LINQ expression, so it would be safer if the retry could be handled at a lower level.

You might be able to accomplish it by creating an extension method that performs the retry for you:

public static List<T> ToList_DeadlockRetry<T>(this IEnumerable<T> source, int retryAttempts = 5)
{
    while (retryAttempts > 0)
    {
        try
        {
            return source.ToList();
        }
        catch (SqlException ex)
        {
            retryAttempts--;

            if (retryAttempts == 0)
            {
                throw ex;
            }
        }
    }
}

Then you could use it like this:

var results = from c in ctx.customers
              from o in c.orders
              from i in o.order_items
              select new { c.lname, c.fname, i.product.product_name, i.quantity };

 return results.ToList_DeadlockRetry();