Database Connection Resiliency in Entity Framework Core

Visual Studio Entity Framework Core

In this post, we will see how to implement database connection resiliency using an inbuilt or custom execution strategy using Entity Framework Core. The connection resiliency feature retries a defaulted database commands automatically.

Applications in the cloud environment are more sensitive to transient faults. So, transient faults are generally temporary in nature and often get corrected on its own. Network connectivity issues, the temporary unavailability of a service or timeouts issues may cause errors in your applications.

For starting, connection resiliency is implemented by supplying an Execution Strategy. In other words, this Execution strategy encapsulates the logic necessary to detect failures and retry failed commands. You can find some details on Microsoft documentation.

Importantly, this resiliency feature can be used with any database like SQL, MySQL, OracleSQL Azure DB and more. Each database provider can supply execution strategies for failure conditions and retry policies to address the needs.

In short in this article we will see both basic and custom execution techniques to implement resiliency in SQL database connections created using Entity Framework Core provider.

Resiliency Using Execution Strategies

For instance, using default or custom Execution strategies, we can implement an efficient resiliency in Entity Framework Core. To clarify, an execution strategy can be defined per database Context basis by configuring the DbContextOptionsBuilder objects.

Default Execution strategy -Enable Retry Pattern

Firstly, update the ConfigureServices() for EnableRetryOnFailure to enable default retrying execution strategy. So, you can configure DbContextOptionsBuilder within Startup.cs as below

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

In addition, you can set the EnableRetry option using the OnConfiguring() method also as below.

builder.Services.AddDbContext<BillingContext>(options =>                
   
 options.UseSqlServer(configuration.GetConnectionString("BillingDatabase"),
     providerOptions => providerOptions.EnableRetryOnFailure()
));

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

builder.Services.AddDbContext<BillingContext>(options =>
   
 options.UseSqlServer(configuration.GetConnectionString("BillingDatabase"),
        providerOptions => providerOptions.EnableRetryOnFailure(
            maxRetryCount: 10,
            maxRetryDelay: TimeSpan.FromSeconds(10),
            errorNumbersToAdd: null
        )
));

Custom Execution strategy

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

Firstly, 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));

		});
	});
}

Another option is to 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. You have an example below

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);
	}
}

Read more…

In conclusion, I published other posts about Entity Framework Core that may be interesting to you:

2 thoughts on “Database Connection Resiliency in Entity Framework Core

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.