The best way to implement database connection resiliency in ASP.NET Core

A high-quality software should be secure, performant, scalable, and dependable. The reliability of an software depends upon many elements, however one of the vital is resiliency, or the flexibility to face up to failures or faults throughout run time. On this article, we’ll see how we are able to convey resiliency to database connections in ASP.NET Core utilizing Entity Framework Core.

EF Core has a characteristic referred to as connection resiliency that robotically retries failed database instructions to take care of connectivity throughout transient errors or community instability. By encapsulating the logic for figuring out failures and retrying instructions, this characteristic permits us to plot execution plans for various database failure conditions.

To make use of the code examples offered on this article, it is best to have Visible Studio 2022 put in in your system. In the event you don’t have already got a replica, you may obtain Visible Studio 2022 right here.

Create an ASP.NET Core Internet API challenge in Visible Studio 2022

To create an ASP.NET Core Internet API challenge in Visible Studio 2022, observe the steps outlined under.

1. Launch the Visible Studio 2022 IDE.
2. Click on on “Create new challenge.”
3. Within the “Create new challenge” window, choose “ASP.NET Core Internet API” from the checklist of templates displayed.
4. Click on Subsequent.
5. Within the “Configure your new challenge” window, specify the identify and placement for the brand new challenge. Optionally verify the “Place resolution and challenge in the identical listing” verify field, relying in your preferences.
6. Click on Subsequent.
7. Within the “Further Data” window, choose “.NET 8.0 (Lengthy Time period Assist)” because the framework model and make sure that the “Use controllers” field is checked. We will probably be utilizing controllers on this challenge.
8. Elsewhere within the “Further Data” window, depart the “Authentication Kind” set to “None” (the default) and make sure the verify packing containers “Allow Open API Assist,” “Configure for HTTPS,” and “Allow Docker” stay unchecked. We gained’t be utilizing any of these options right here.
9. Click on Create.

We’ll use this ASP.NET Core Internet API challenge to work with the code examples within the sections under.

Create an execution technique in EF Core

In EF Core, an execution technique is outlined as a part that encapsulates the logic for dealing with database command execution errors and retrying them if the errors are deemed transitory. An execution technique permits builders to verify their purposes can gracefully get better from transient errors with out requiring human intervention.

You’ll be able to create an execution technique utilizing the CreateExecutionStrategy methodology as proven within the code snippet given under.

var technique = _context.Database.CreateExecutionStrategy();
await technique.ExecuteAsync(async () =>
{
   await utilizing var transaction = await _context.Database.BeginTransactionAsync();
   //Write your customized code right here to carry out CRUD operations
   //in opposition to the database
   await transaction.CommitAsync();
});

As you may see from the next code instance, an execution technique is often specified within the OnConfiguring methodology of your customized DbContext class.

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

In the event you’re utilizing Azure SQL Database, EF Core already gives the resiliency and retry logic on your database. Nonetheless, you should allow the EF Core execution technique for every DbContext connection your software makes if you wish to leverage EF Core connection resiliency. The next code snippet illustrates how one can allow resilient SQL connections utilizing EF Core which might be retried every time the database connection goes down.

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

Use connection resiliency with transactions in EF Core

In the event you’ve enabled retries in EF Core, each name to the SaveChanges methodology will probably be retried as a unit if a database connection failure occurse. Nonetheless, if you happen to execute a transaction block in your software’s code, utilizing the BeginTransaction methodology, you should invoke an execution technique explicitly utilizing a delegate to make sure that each operation contained in the transaction is executed. That is proven within the code snippet given under.

var technique = db.Database.CreateExecutionStrategy();
technique.Execute(
    () =>
    {
        utilizing var context = new LibraryContext();
        utilizing var transaction = context.Database.BeginTransaction();
        context.Books.Add(new Ebook { Id = 1, Title = "Allow us to C" });
        context.SaveChanges();
        context.Books.Add(new Ebook { Id = 2, Title = "Mastering C# 8.0" });
        context.SaveChanges();
        transaction.Commit();
    });

Deal with database connection failures in ASP.NET Core

When working with database connections utilizing EF Core, it is best to deal with potential connection failures by catching exceptions and retrying the database operations. Think about the next entity class referred to as Buyer.

public class Buyer
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Handle { get; set; } = string.Empty;
    public string Metropolis { get; set; } = string.Empty;
    public string PostalCode { get; set; } = string.Empty;
    public string Nation { get; set; } = string.Empty;
    public string Telephone { get; set; } = string.Empty;
}

The next code itemizing illustrates the DbConnectService class that implements the IDbConnectService interface and exhibits how connection failures and retry operations could be carried out.

public class DbConnectService : IDbConnectService
{
    non-public readonly CustomDbContext _dbContext;
    public DbConnectService(CustomDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    public async Job<Buyer> GetCustomer(int customerId)
    {
        strive
        {
            return await _dbContext.Clients.FindAsync(customerId);
        }
        catch (SqlException ex)
        {
            //Write your customized code right here to deal with
            // connection failure and retry the operation
            // or implement a fallback technique
        }
        return await Job.FromResult<Buyer>(null);
    }
}

The supply code of the IDbConnectService interface is given under.

public interface IDbConnectService
{
    public Job<Buyer> GetCustomer(int customerId);
}

Create a CustomDbContext class in EF Core

As famous above, you’ll often specify your execution technique within the OnConfiguring methodology of your customized DbContext class. The next code itemizing illustrates the CustomDbContext class that extends the DbContext class of EF Core and implements the OnConfiguring and OnModelCreating strategies.

public class CustomContext : DbContext
{
    public DbSet<Buyer> Clients { get; set; }
    public CustomContext(DbContextOptions choices) : base(choices)
    {
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
         optionsBuilder
           .UseSqlServer(
              @"Server= mssqldb;Database=Check;Trusted_Connection=True",
              choices => choices.EnableRetryOnFailure());
     }
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
        //Write your customized code right here to
        //configure the fashions utilized in your software
     }
}

An effective way to deal with database connection failures and make your software resilient is to make use of Polly, a fault-handling library for .NET. You should use Polly to implement circuit breaker performance in order that any database connection failures within the software are dealt with gracefully. I’ll focus on utilizing Polly to implement circuit-breakers in a future submit right here.