https://github.com/toreaurstadboss/BulkOperationsEntityFramework
first off, the ExecutionStrategy is set up. A DbConfiguration is added to set this up.
ApplicationDbModelConfiguration.cs
using System;
using System.Data.Entity;
using System.Data.Entity.SqlServer;
namespace BulkOperationsEntityFramework
{
public class ApplicationDbConfiguration : DbConfiguration
{
public ApplicationDbConfiguration()
{
SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, () =>
new CustomSqlAzureExecutionStrategy(maxRetryCount: 10, maxDelay: TimeSpan.FromSeconds(5))); //note : max total delay of retries is 30 seconds per default in SQL Server
}
}
}
In EF Core 8 (using Entity Framework with .NET 8, you can set it up like this :
Program.cs
public class ApplicationDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
"DefaultConnection",
sqlOptions =>
{
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 10,
maxRetryDelay: TimeSpan.FromSeconds(5),
errorNumbersToAdd: null
);
});
}
Setting up the interval strategy
The CustomSqlAzureExecutionStrategy inherits from the SqlAzureExecutionStrategy. The default base delay in Entity Framework is to wait one second, so the next wait time will be about 2 seconds, then the next delays will quickly grow up the max wait time of five seconds. The custom sql azure execution strategy implementation inherits from SqlAzureExecutionStrategy.CustomSqlAzureExecutionStrategy.cs
using System;
using System.Data.Entity.SqlServer;
namespace BulkOperationsEntityFramework
{
public class CustomSqlAzureExecutionStrategy : SqlAzureExecutionStrategy
{
[ThreadStatic]
private static int _currentRetryCount = 0;
public CustomSqlAzureExecutionStrategy(int maxRetryCount, TimeSpan maxDelay)
: base(maxRetryCount, maxDelay) { }
protected override bool ShouldRetryOn(Exception ex)
{
_currentRetryCount++;
Console.WriteLine($"{nameof(CustomSqlAzureExecutionStrategy)}: Retry-count within thread: {_currentRetryCount}");
Log.Information("{Class}: Retry-count within thread: {RetryCount} {ExceptionType}", nameof(CustomSqlAzureExecutionStrategy), _currentRetryCount, ex.GetType().Name);
return base.ShouldRetryOn(ex) || ex is SimulatedTransientSqlException;
}
}
}
Of course, just logging out to console probably is not a very elegant solution, and it could instead be logged out to for example SeriLog, which is used in the line with the Log.Information call.
The SimulatedTransientSqlException looks like this:
SimulatedTransientSqlException.cs
public class SimulatedTransientSqlException : Exception
{
public SimulatedTransientSqlException()
: base("Simulated transient SQL exception.") { }
}
The following db interceptor is added to simulate transient failures happening, at 10% chance of it happening.
TransientFailureInterceptor.cs
using System;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Diagnostics;
namespace BulkOperationsEntityFramework
{
public class TransientFailureInterceptor : DbCommandInterceptor
{
private static readonly Random _random = new Random();
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
SimulateTransientFailure(interceptionContext);
base.ReaderExecuting(command, interceptionContext);
}
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
SimulateTransientFailure(interceptionContext);
base.ScalarExecuting(command, interceptionContext);
}
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
SimulateTransientFailure(interceptionContext);
base.NonQueryExecuting(command, interceptionContext);
}
private void SimulateTransientFailure<TResult>(DbCommandInterceptionContext<TResult> context)
{
// Simulate a transient failure 10% of the time
double r = _random.NextDouble();
if (r < 0.1)
{
var ex = new SimulatedTransientSqlException();
string info = "Throwing a transient SqlException. ";
Trace.WriteLine($"{info} {ex.ToString()}");
context.Exception = ex;
}
}
}
public class SimulatedTransientSqlException : Exception
{
public SimulatedTransientSqlException()
: base("Simulated transient SQL exception.") { }
}
}
Next up connecting the dots in the DbContext, setting up the db configuration
ApplicationDbContext.cs
[DbConfigurationType(typeof(ApplicationDbConfiguration))]
public class ApplicationDbContext : DbContext
{
static ApplicationDbContext()
{
if (!AppDomain.CurrentDomain.GetAssemblies().Any(a => a.FullName.StartsWith("Effort")))
{
DbInterception.Add(new TransientFailureInterceptor()); //add an interceptor that simulates a transient connection error occuring (30% chance of it happening)
DbInterception.Add(new SerilogCommandInterceptor()); //do not add logging if EF6 Effor is used (for unit testing)
}
}
//more code..
Also note that you usually do not want to add the TransientFailureInterceptor, it is just added for testing. You could for example add a boolean property on your DbContext to set if you are testing out connection resiliency and add the TransientFailureInterceptor when you can to test it, or provide a public method to add the TransientfailureInterceptor, and remove it afterwards if desired. Within a Test-project, you should be able to test out connection resiliency.
