How to implement database connection resiliency in ASP.NET Core

This feature helps ASP.NET Core applications overcome transient faults, such as temporary network issues or database unavailability, ensuring stable and continuous connectivity.

In EF Core, an execution strategy is a component that handles database command errors by retrying them when the errors are considered transient. This strategy enables applications to automatically recover from temporary issues without human intervention. To implement such a strategy, developers use the CreateExecutionStrategy method.

Creating an Execution Strategy

In the example below, an execution strategy is created and used to handle database operations with retries in case of transient errors:

var strategy = _context.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using var transaction = await _context.Database.BeginTransactionAsync();
    _context.Books.Add(new Book { Id = 1, Title = "Database Design" });
    await _context.SaveChangesAsync();
    transaction.Commit();
});

This code can be included in the OnConfiguring method of your custom DbContext class, as shown here:

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

Enabling Connection Resiliency in EF Core

EF Core provides built-in resiliency and retry logic for Azure SQL Database. To enable this, ensure that retries are configured for each DbContext connection as shown below:

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

Transactions with Connection Resiliency

When transactions are used, retries need to be handled explicitly using an execution strategy.

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 = "Intro to C#" });
    context.SaveChanges();
    context.Books.Add(new Book { Id = 2, Title = "Mastering C#" });
    context.SaveChanges();
    transaction.Commit();
});

Handling Database Connection Failures in ASP.NET Core

To handle connection failures, EF Core allows developers to catch exceptions and implement retry logic.

Product Entity Class

The Product class represents a product in the system with its properties like name, description, price, and stock quantity.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
}

IDbConnectService Interface

The interface defines the method GetProductAsync for retrieving a product by its ID. It will be implemented in the DbConnectService class to handle database queries and manage retry logic.

public interface IDbConnectService
{
    Task<Product> GetProductAsync(int productId);
}

DbConnectService Class

The DbConnectService class implements the IDbConnectService interface. It includes the logic for handling database connection errors and retrying the operation if needed.

public class DbConnectService : IDbConnectService
{
    private readonly CustomDbContext _dbContext;
    private const int MaxRetries = 3;  // Maximum number of retry attempts

    public DbConnectService(CustomDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<Product> GetProductAsync(int productId)
    {
        int attempt = 0;
        while (attempt < MaxRetries)
        {
            try
            {
                // Attempt to retrieve the product from the database
                return await _dbContext.Products.FindAsync(productId);
            }
            catch (SqlException ex)
            {
                attempt++;
                Console.WriteLine($"Attempt {attempt} failed: {ex.Message}");

                // If we've reached the max number of retries, throw the exception
                if (attempt >= MaxRetries)
                {
                    Console.WriteLine("Max retry attempts reached. Could not fetch product.");
                    throw; // Rethrow the exception after max retries
                }

                // Wait for a brief period before retrying
                await Task.Delay(1000);  // Wait 1 second before retrying
            }
        }

        // Return null if all retry attempts fail
        return null;
    }
}

Creating a CustomDbContext Class in EF Core

The CustomDbContext class typically configures the execution strategy and models in its OnConfiguring and OnModelCreating methods.

public class CustomContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public CustomContext(DbContextOptions options) : base(options)
    {
    }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Custom model configurations
    }
}

While EF Core provides built-in retries for transient errors, you can use Polly, a .NET library, to add more advanced fault-handling strategies like circuit breakers. Polly can work alongside EF Core's retries to create even more robust failure-handling mechanisms.