Database Connection Resiliency in Entity Framework ASP.NET Core

In this post, we will see how to implement Database Connection Resiliency in Entity Framework using an inbuilt or custom execution strategy using EFCore in ASP.NET Core.

Today in this article, we will cover below aspects,

The connection resiliency feature retries defaulted database commands automatically.

Enable Execution Strategies – Best Practices

An application communicating especially in the cloud environment is more sensitive to transient faults.

Transient faults are generally temporary and often get corrected on their own.

Such issues may be caused due to network connectivity issues the temporary unavailability of a service, timeout issues, etc.

As a good practice, one should always design for Connection resiliency.

Connection resiliency is implemented by supplying an Execution Strategy.

This Execution strategy encapsulates the logic necessary to detect failures and retry failed commands.

This resiliency feature can be used with any database like SQL, MySQL, Oracle, SQL Azure DB, etc.

Each database provider like SQL, and MySQL can supply execution strategies for failure conditions and retry policies to address the needs.

There are multiple ways to handle the SQL connection resiliency like using retry or custom execution techniques etc.

Today in this article we will see both basic and custom execution techniques to implement resiliency in SQL database connections created using the EFCore provider.

Let’s get started with different approaches one can use to address the resiliency needs in EFCore.

I shall be using ASP.NET Core 3.1 or .NET Core 6.0 API for implementing the Connection resiliency.

Resiliency Using Execution Strategies

Resiliency in EFcore can be implemented using default or custom Execution strategies.

An execution strategy can be defined per database Context basis by configuring the DbContextOptionsBuilder objects.

Default Execution strategy -Enable Retry Pattern for database connection

Update the ConfigureServices() for EnableRetryOnFailure to enable default retrying execution strategy. DbContextOptionsBuilder can be configured within Startup.cs as below,

          services.AddDbContext<EmployeeContext>(options =>
            {
             
 options.UseSqlServer(Configuration.GetConnectionString("EmployeeDB"),
                sqlServerOptionsAction: sqlOptions =>
                {
                    sqlOptions.EnableRetryOnFailure();
                });
            });

Or

You can set the EnableRetry option using the OnConfiguring() method also as below.

Additionally, one can use EnableRetryOnFailure() overloaded method for specifying the option like Maximum retry count or maximum retry delay etc.

Custom Execution strategy

If you wish to register a custom execution strategy then please see below code example on achieving that,

Update the ConfigureServices() within Startup.cs as below for using any CustomExecutionStrategy().

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddDbContext<EmployeeContext>(options =>
            {
       options.UseSqlServer(Configuration.GetConnectionString("EmployeeDB"),
                sqlServerOptionsAction: sqlOptions =>
                {
                    sqlOptions.ExecutionStrategy(c => new CustomExecutionStrategy(c));
                });
            });
        }

Or

Update the OnConfiguring () as below for using any CustomExecutionStrategy()

     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {             optionsBuilder.UseSqlServer("Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;",
                    builder => builder.ExecutionStrategy(c => new CustomExecutionStrategy(c)));
            }
        }

How to define CustomExecutionStrategy

The custom strategy class derived using class ExecutionStrategy is defined as below Sample example,

  public class CustomExecutionStrategy : ExecutionStrategy
    {
        public CustomExecutionStrategy(DbContext context) : base(context, ExecutionStrategy.DefaultMaxRetryCount, ExecutionStrategy.DefaultMaxDelay)
        {
        }
        public CustomExecutionStrategy(ExecutionStrategyDependencies dependencies) : base(dependencies, ExecutionStrategy.DefaultMaxRetryCount, ExecutionStrategy.DefaultMaxDelay)
        {
        }
        public CustomExecutionStrategy(DbContext context, int maxRetryCount, TimeSpan maxRetryDelay) :
            base(context, maxRetryCount, maxRetryDelay)
        {
        }
        protected override bool ShouldRetryOn(Exception exception)
        {
            return exception.GetType() == typeof(DbUpdateException);
        }
    }

Database operation using transactions for Example- defined with BeginTransaction() needs to be addressed differently i.e manually invoking the Execution strategy with a delegate.

Such execution strategy should encapsulate everything that needs to be executed.

For more details: Connection Resiliency for Transaction in EFCore- Part II

Other references:

• Connection Resiliency for Transaction in EFCore- Part II

• Getting Started with EFCore in ASP.NET Core

That’s all Happy Coding!

Please sound off your comments below if any.

Summary

Today in this article we looked at how to design for Database Connection resiliency addressing connection failures like transient errors or network or hardware issues and retry failed commands. We looked at the default execution strategies and custom execution strategies techniques for enabling a retry pattern in database operations.

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.