Connection Resiliency

Note

This documentation is for EF Core. For EF6.x, see Entity Framework 6.

Connection resiliency automatically retries failed database commands. It is specific to relational databases.

Note

Connection resiliency was introduced in EF Core 1.1.0. If you are using an earlier release, the functionality shown in this article will not be available.

SQL Server

The SQL Server provider includes an execution strategy that is specifically tailored to SQL Server (including SQL Azure). It is aware of the exception types that can be retried and has sensible defaults for maximum retries, delay between retries, etc.

An execution strategy is specified when configuring the options for your context. This is typically in the OnConfiguring method of your derived context, or in Startup.cs for an ASP.NET Core application.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFMiscellanous.ConnectionResiliency;Trusted_Connection=True;",
            options => options.EnableRetryOnFailure());
}

Custom execution strategy

There is a mechanism to register a custom execution strategy of your own.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseMyProvider(
            "<connection string>",
            options => options.ExecutionStrategy(...));
}

Execution strategies and transactions

An execution strategy that automatically retries on failures needs to be able to play back each operation that fails. When retries are enabled, each operation you perform via EF Core becomes its own retriable operation, i.e. each query and each call to SaveChanges() will be retried as a unit if a transient failure occurs.

However, if your code initiates a transaction using BeginTransaction() you are defining your own group of operations that need to be treated as a unit, i.e. everything inside the transaction would need to be played back shall a failure occur. You will receive an exception like the following if you attempt to do this when using an execution strategy.

InvalidOperationException: The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.

The solution, is to manually invoke the execution strategy with a delegate representing everything that needs to be executed. If a transient failure occurs, the execution strategy will invoke the delegate again.

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

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

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

            transaction.Commit();
        }
    });
}