I am currently using the following execution strategy, the 'set it and forget it' approach. It usually works great!

 options.UseSqlServer(connection, sqlOptions =>
 {
     sqlOptions.EnableRetryOnFailure();
 });

I need to be able to suspend / replace the configured IExecutionStrategy for certain cases where I need to use an explicit transaction.

A solution therefore is needed to avoid the following exception:

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 exception is described in Connection Resiliancy as being a problem if you're using an explicit BeginTransaction. The solution provided is to use this line of code db.Database.CreateExecutionStrategy().Execute(() => ... ) instead of an explicit or ambient transaction.

This solution can't be used if you need two active transactions for operations in two different databases. Here's a simplification of what I am logically trying to achieve:

• Perform an operation in one database.
• Perform an operation in a second database (not a different context).
• Roll them both back if they don't both succeed.

Since we don't have distributed transactions yet in EF Core, I wanted to create two transaction scopes and then commit them together. The following code will work, but only if you do not have a retrying execution strategy defined.

// The contexts represent TWO DIFFERENT databases on Azure
// Everything with a 1 is the first database
// Everything with a 2 is the second database
using (var tran1 = _context1.Database.BeginTransaction())
{
    using (var tran2 = _context2.Database.BeginTransaction())
    {
        // Create a thing1 and insert it into the table in DB1
        var thing1 = new Thing1();
        _context1.Thing1s.Add(thing1);

        // Save it to get the DB generated identity key 
        await _context1.SaveChangesAsync();

        // Now create a thing2 which requires the key for the thing1 we just inserted
        var thing2 = new Thing2()
        {
            thing1ID = thing1.ID
        };

        // Insert into DB 2 and save changes
        _context2.Thing2s.Add(thing2);
        await _context2.SaveChangesAsync();

        // commit both if everything worked
        await tran1.CommitAsync();
        await tran2.CommitAsync();
    }
}

For my needs this is an acceptable low-risk alternative to fully distributed transactions. I lose the auto-retry but I don't see any other alternative at this time.

The recommended solution (db.Database.CreateExecutionStrategy().Execute(() => ... )) unfortunately does not help in this situation since both transaction scopes need to remain active until they are committed.

I've looked through some of the source to try to find solutions. Here are my attempts and the limitations I came across:

Attempt 1: Use NonRetryingExecutionStrategy

I found NonRetryingExecutionStrategy which does exactly what I want, however there is no constructor taking a DbContext. The constructor takes ExecutionStrategyDependencies which is basically impossible for me to construct in my own code.

Suggestion 1: Add a new constructor to NonRetryingExecutionStrategy

This would allow me to do this in the scope of a preexisting transaction.

await new NonRetryingExecutionStrategy(_context1).ExecuteAsync(() => ...)

Attempt 2: Use SqlServerRetryingExecutionStrategy with 0 retries

Next I attempted to do the following:

await new SqlServerRetryingExecutionStrategy(_context, 0).ExecuteAsync(() => ...)

This should work since it's no longer retrying anything (and that's the whole reason the exception is thrown).

This unfortunately does not work since the OnFirstExecution method in ExecutionStrategy (which is the base class) will ALWAYS throw the exception if there is an active transaction.

Suggestion 2: Do not throw the exception if retries are disabled (RetriesOnFailure == false)

In other words update ExecutionStrategy.OnFirstExecution to the following:

protected virtual void OnFirstExecution()
{
    if (RetriesOnFailure == false) 
    { 
        // do not throw exception
    }
    else { 
        // existing logic
    }
 } 

Then RetriesOnFailure would need to consider the MaxRetryCount

public virtual bool RetriesOnFailure
        => !Suspended && MaxRetryCount > 0;

Attempt 3: Create my own custom execution strategy

This succeeded! My implementation is minimal, but with the following override.

    protected override void OnFirstExecution()
    {
         // do nothing!!
    }

While this worked it quite frankly took me all day to first be reminded (and annoyed!) that distributed transactions aren't yet supported, and then fail to find an existing solution.

PS: I'm pretty sure this succeeded in my brief testing! If there's some reason why this wouldn't work I'd love to hear it :-)

Attempt 4: Make other ways to create an execution strategy on the fly

The most discoverable way would be to add another way to create an execution strategy on the DatabaseFacade.

Perhaps DbContext.Database.CreateNonRetryingExecutionStrategy() ?

This could then be documented and it would be completely obvious what it was.

Conclusions

It was simply too hard to disable the configured execution strategy and there needs to be an easier way.
My attempted solutions are not mutually exclusive.

Even with the best solution I came up with, I still need to wrap every group of database operation in ExecuteAsync. If there were an alternative way to disable the configured strategy for the whole context that would be preferable.

Disclaimer:

Clearly things are always more complicated so I may have completely missed something.