Resilient Connections in Entity Framework Core

When you work with databases in your application, you may face connection issues from time to time which is beyond our control. When this happens normally the application will raise a connection timeout or server not available exception. In Entity Framework core you can overcome this kind of scenario by setting up resilient connections with exponential retries. 

The code snippet given below will retry to connect up to 10 times in case of a failure with a delay of 30 seconds in-between each try.

services.AddDbContext(o =>
{
    o.UseSqlServer(connectionString,
        sqlServerOptionsAction: options =>
        {
            options.EnableRetryOnFailure(maxRetryCount: 10,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
        });
});	

Also, when you enable retries in EF Core connections, each operation you perform will become its own retriable operation. So that means whenever we perform a query or a call to the SaveChanges method, it will be retried as a unit during a transient failure scenario. But when you initiate a transaction block in your code using BeginTransaction, then you are defining your own group that needs to treated as a single unit.

So, in this case, you will need to manually invoke an execution strategy with a delegate method that contains all that need to be executed as a block. So, when a transient failure occurs, the execution strategy will invoke the delegate again as part of the retry operation.

var strategy = blogContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{

    using (var transaction = blogContext.Database.BeginTransaction())
    {
        blogContext.PostItems.Update(postItem);
        await blogContext.SaveChangesAsync();


        if (raisePostChangedEvent)
        await eventLogService.SaveEventAsync(postChangedEvent);
        transaction.Commit();
    }

Reference : https://docs.microsoft.com/en-us/dotnet/standard/modern-web-apps-azure-architecture/work-with-data-in-asp-net-core-apps