In this blog post, we’ll explore the intricacies of ASP.NET Core EF Core and delve into effective strategies for handling connection retries when dealing with SQL Server databases. By implementing retry mechanisms, we can enhance the robustness of our applications and ensure seamless interactions with SQL Server databases.

EF Core has built-in retry functionality. To use it, you can call options.EnableRetryOnFailure(), like this.

builder.Services.AddDbContextPool<BookstoreDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("BookstoreDbConnection"), 
        options => options.EnableRetryOnFailure());
});

The retry logic is contained in execution strategy classes. The above code is using the default execution strategy class.

We will be able to customize the execution strategy. The following code customize the execution strategy.

builder.Services.AddDbContextPool<BookstoreDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("BookstoreDbConnection"), 
        options => options.EnableRetryOnFailure(5, TimeSpan.FromSeconds(40), default));
});

This execution strategy will retry 5 times, 30 seconds delay and list of error codes is null. Here the application running without Sql Server service running. Application is trying to connect to Sql server 5 times and after 5 tries throwing exception.

We can customize more by inheriting the SqlServerRetryingExecutionStrategy class. We can use the inherited class like this.

builder.Services.AddDbContextPool<BookstoreDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("BookstoreDbConnection"),
        options =>
        {
            options.ExecutionStrategy(c => new CustomSqlServerRetryingExecutionStrategy(c, 5, TimeSpan.FromSeconds(30), default));
        });
});

In this blog post, we explored the importance of building reliable ASP.NET Core applications, focusing on effective handling of SQL Server connection retries. By implementing these techniques, we can ensure robust and resilient applications that gracefully handle transient failures in SQL Server connections.

Happy Programming.