How to implement database connection resiliency in ASP.NET Core

A high-quality application must be stable, performant, scalable, and reliable. The reliability of an application depends on many factors, but one of the most important is resiliency, or the ability to withstand failures or faults during run time. In this article, we’ll see how we can bring resiliency to database connections in ASP.NET Core using Entity Framework Core.

EF Core has a feature called connection resiliency that automatically retries failed database commands to maintain connectivity during transient errors or network instability. By encapsulating the logic for identifying failures and retrying commands, this feature allows us to devise execution plans for different database failure situations.

To use the code examples provided in this article, you should have Visual Studio 2022 installed in your system. If you don’t already have a copy, you can download Visual Studio 2022 here.

Create an ASP.NET Core Web API project in Visual Studio 2022

To create an ASP.NET Core Web API project in Visual Studio 2022, follow the steps outlined below.

Launch the Visual Studio 2022 IDE.
Click on “Create new project.”
In the “Create new project” window, select “ASP.NET Core Web API” from the list of templates displayed.
Click Next.
In the “Configure your new project” window, specify the name and location for the new project. Optionally check the “Place solution and project in the same directory” check box, depending on your preferences.
Click Next.
In the “Additional Information” window, select “.NET 8.0 (Long Term Support)” as the framework version and ensure that the “Use controllers” box is checked. We will be using controllers in this project.
Elsewhere in the “Additional Information” window, leave the “Authentication Type” set to “None” (the default) and ensure the check boxes “Enable Open API Support,” “Configure for HTTPS,” and “Enable Docker” remain unchecked. We won’t be using any of those features here.
Click Create.

We’ll use this ASP.NET Core Web API project to work with the code examples in the sections below.

Create an execution strategy in EF Core

In EF Core, an execution strategy is defined as a component that encapsulates the logic for handling database command execution errors and retrying them if the errors are deemed transitory. An execution strategy allows developers to make sure their applications can gracefully recover from transient errors without requiring human intervention.

You can create an execution strategy using the CreateExecutionStrategy method as shown in the code snippet given below.

var strategy = _context.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
   await using var transaction = await _context.Database.BeginTransactionAsync();
   //Write your custom code here to perform CRUD operations
   //against the database
   await transaction.CommitAsync();
});

As you can see from the following code example, an execution strategy is usually specified in the OnConfiguring method of your custom DbContext class.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server= mssqldb;Database=Test;Trusted_Connection=True",
            options => options.EnableRetryOnFailure());
}

If you’re using Azure SQL Database, EF Core already provides the resiliency and retry logic for your database. However, you must enable the EF Core execution strategy for each DbContext connection your application makes if you want to leverage EF Core connection resiliency. The following code snippet illustrates how you can enable resilient SQL connections using EF Core that are retried whenever the database connection goes down.

builder.Services.AddDbContext<LibraryContext>(options =>
    {
        options.UseSqlServer(builder.Configuration["IDGConnectionString"],
        sqlServerOptionsAction: sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(45));
        });
    });

Use connection resiliency with transactions in EF Core

If you’ve enabled retries in EF Core, every call to the SaveChanges method will be retried as a unit if a database connection failure occurse. However, if you execute a transaction block in your application’s code, using the BeginTransaction method, you must invoke an execution strategy explicitly using a delegate to ensure that every operation inside the transaction is executed. This is shown in the code snippet given below.

var strategy = db.Database.CreateExecutionStrategy();
strategy.Execute(
    () =>
    {
        using var context = new LibraryContext();
        using var transaction = context.Database.BeginTransaction();
        context.Books.Add(new Book { Id = 1, Title = "Let us C" });
        context.SaveChanges();
        context.Books.Add(new Book { Id = 2, Title = "Mastering C# 8.0" });
        context.SaveChanges();
        transaction.Commit();
    });

Handle database connection failures in ASP.NET Core

When working with database connections using EF Core, you should handle potential connection failures by catching exceptions and retrying the database operations. Consider the following entity class called Customer.

public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Address { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string PostalCode { get; set; } = string.Empty;
    public string Country { get; set; } = string.Empty;
    public string Phone { get; set; } = string.Empty;
}

The following code listing illustrates the DbConnectService class that implements the IDbConnectService interface and shows how connection failures and retry operations can be implemented.

public class DbConnectService : IDbConnectService
{
    private readonly CustomDbContext _dbContext;
    public DbConnectService(CustomDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    public async Task<Customer> GetCustomer(int customerId)
    {
        try
        {
            return await _dbContext.Customers.FindAsync(customerId);
        }
        catch (SqlException ex)
        {
            //Write your custom code here to handle
            // connection failure and retry the operation
            // or implement a fallback strategy
        }
        return await Task.FromResult<Customer>(null);
    }
}

The source code of the IDbConnectService interface is given below.

public interface IDbConnectService
{
    public Task<Customer> GetCustomer(int customerId);
}

Create a CustomDbContext class in EF Core

As noted above, you will usually specify your execution strategy in the OnConfiguring method of your custom DbContext class. The following code listing illustrates the CustomDbContext class that extends the DbContext class of EF Core and implements the OnConfiguring and OnModelCreating methods.

public class CustomContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public CustomContext(DbContextOptions options) : base(options)
    {
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
         optionsBuilder
           .UseSqlServer(
              @"Server= mssqldb;Database=Test;Trusted_Connection=True",
              options => options.EnableRetryOnFailure());
     }
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
        //Write your custom code here to
        //configure the models used in your application
     }
}

A great way to handle database connection failures and make your application resilient is to use Polly, a fault-handling library for .NET. You can use Polly to implement circuit breaker functionality so that any database connection failures in the application are handled gracefully. I’ll discuss using Polly to implement circuit-breakers in a future post here.

READ SOURCE