The configured execution strategy 'SqlAzureExecutionStrategy' does not support user initiated transactions

Question

I am using the latest v6 of Entity Framework along with a UnitOfWork pattern. This has been fine on a server for the past few years.

I want to move to azure hosting and use SQLAzure, so started to migrate the app. However I have had a number of issues.

Firstly I kept getting this error intermittently

A transport-level error has occurred when receiving results from the server.

After some Googling, it seems this is common and you need to implement your own SqlAzureExecutionStrategy - All seems fine. Until I then find out that it doesn't support initiated transactions!

I then stumbled on this blog post - Which outlines the exact problems and gives example code of how to fix issues (Or so I thought).

I have followed the post (As far as I'm aware) exactly. I have my dBconfiguration class setup and it's hitting it the SetExecutionStrategy on app start.

public class EfConfig : DbConfiguration
{
    public EfConfig()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy
              ? (IDbExecutionStrategy)new DefaultExecutionStrategy()
              : new CustomSqlAzureExecutionStrategy());
    }

    public static bool SuspendExecutionStrategy
    {
        get { return (bool?)CallContext.LogicalGetData("SuspendExecutionStrategy") ?? false; }
        set { CallContext.LogicalSetData("SuspendExecutionStrategy", value); }
    }
}

Then I have a custom class as referenced above called 'CustomSqlAzureExecutionStrategy' which I have put below and have overridden the ShouldRetryOn method

public class CustomSqlAzureExecutionStrategy : SqlAzureExecutionStrategy
{
    protected override bool ShouldRetryOn(Exception exception)
    {
        var shouldRetry = false;

        var sqlException = exception as SqlException;
        if (sqlException != null)
        {
            foreach (SqlError error in sqlException.Errors)
            {
                if (error.Number == -2)
                {
                    shouldRetry = true;   
                }

            }
        }
        shouldRetry = shouldRetry || base.ShouldRetryOn(exception);
        return shouldRetry;
    }
}

However, when I run my app I still get the same error I started with, but this time just pointing to the custom class?

The configured execution strategy 'CustomSqlAzureExecutionStrategy' does not support user initiated transactions.

Have I missed something obvious here? Or not understood something? Any help would be greatly appreciated.

Update

---

Typically... StackOverFlow rubber ducking. I actually read it properly and found that I need to manually set the SuspendExecutionStrategy in my UnitOfWork (Before BeginTransaction and after Commit).

So I have this just before .BeginTransaction()

EfConfig.SuspendExecutionStrategy = true;

And this just after .Commit()

EfConfig.SuspendExecutionStrategy = false;

This allows me to run the application now, but I still (Rarely I might add) get the transient error message?

A transport-level error has occurred when receiving results from the server.

Answer 1

In addition to suspending the execution strategy you also need to wrap the action you are retrying including the transaction in a manual call to the execution strategy, see my answer to How to use SqlAzureExecutionStrategy and "Nolock"

Answer 2

make sure you wrap the whole thing in a try/finally to be sure you set the suspend flag back to false. also have a look at this link for a detailed explanation: help link