Resilient Entity Framework Core SQL Connections

SQL connections resilient in EF Core is way to easy to setup. With a few lines of code we can have things like try to connect X amount of time if connection fails the first time. Besides, connection resiliency we can add transactions strategies. so if a total failure happens we no data is affected.

When retries are enabled in EF Core connections, each operation you perform using EF Core becomes its own re-tryable operation. Each query and each call to SaveChanges will be retried as a unit if a transient failure occurs.

This is how it’s done. Add/modify the following on your Startup.cs services configurations.

services.AddDbContext(options =>
{
   options.UseSqlServer(Configuration["ConnectionString"],
   sqlServerOptionsAction: sqlOptions =>
   {
      sqlOptions.EnableRetryOnFailure(
         maxRetryCount: 10,
         maxRetryDelay: TimeSpan.FromSeconds(30),
         errorNumbersToAdd: null)
    });
 });

The options we have setup are Maximum Retry count to be 10, Maximum Delay per each retry to 30 seconds, and additional SQLerror number that should be considered transient to be NULL. So, basically what’s going to happen is that I my SQL connection will try up to 10 times and each connection will have a delay of 30 seconds between tries.

Now, to make this even more awesome, we add transactions.

using (var db = new YourContext())
{
    var strategy = db.Database.CreateExecutionStrategy();

    strategy.Execute(() =>
    {
        using (var context = new YourContext())
        {
            using (var transaction = context.Database.BeginTransaction())
            {
                context.Blogs.Add(new Blog {Url = "http://blogs.msdn.com/dotnet"});
                context.SaveChanges();

                context.Blogs.Add(new Blog {Url = "http://blogs.msdn.com/visualstudio"});
                context.SaveChanges();

                transaction.Commit();
            }
        }
    });
}

References
https://docs.microsoft.com/en-us/dotnet/standard/microservices-architecture/implement-resilient-applications/implement-resilient-entity-framework-core-sql-connection

In conclusion, having a resilient SQL connection can save us for those annoying connection drops or waiting for a SQL connection to be available in the pool.

Spread the word
  • Yum