Repeated logic for LINQ-TO-SQL on SQL Azure - efficient implementations?

Question

I have a fairly large web application using LINQ-TO-SQL running on Azure and am experiencing Transient errors from SQL-Azure and therefore you need to retry. I know the Transient Fault Handling Framework and a few sites that provide usage examples, but it looks like you need to wrap all your LINQ queries in something like 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, as well as the fact that many times the query is not actually executed until the results are listed. For example, most of my functions in my data layer return IQueryable <> to the business level (which makes them more flexible than returning a list). So this means that you have to lure your level of business logic with re-database logic - ugly.

So, I think, in order to preserve the retry logic in the data layer, I would have to put .ToList () on all my requests so that they execute right there, and not in the layer above.

I am very sorry that there is a way to implement the retry logic in some base class and it is not necessary to change all my requests. It seems that EF will also have this problem.

Is the real answer to try talking to the SQL-Azure team to automatically retry, so we don’t have to worry about that in our code?

Answer 1

After doing something similar, I went ahead and made it a library: https://github.com/daveaglick/LinqToSqlRetry (MIT license and available on NuGet).

You can repeat the calls to SubmitChanges() by writing SubmitChangesRetry() instead:

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

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

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

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

 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 call to func() and the retryPolicy object retryPolicy provided based on usage. It just gives you an idea of ​​what happens during a repeat loop. Just check out the repository for more information.

Answer 2

I am not aware of a good solution, since LINQ to SQL does not allow us to intercept queries. But a small code refactor may help. Something like (pseudo-code):

 public Result QueryWithRetry(IQueryable query) { RetryPolicy retry = new RetryPolicy<MyRetryStrategy>(5, TimeSpan.FromSeconds(5)); (() => { return retry.ExecuteAction(query); } } 

Now it's a little easier to call this method:

Result = QueryWithRetry (... LINQ query here ...);

However, it is still necessary to change the code and change each request.

Best wishes,

Ming Xu.