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.

Lemberg

Unfortunately, I do not have a real answer to your problem. But I would suggest to add some logging to your code, especially to the constructor of EfConfig and to the getter and setter of SuspendExecutionStrategy. This helps me in most cases to understand the problem. – Ayer

I suppose EfConfig.SuspendExecutionStrategy = true disables the retry strategy for the whole application. Between those two statements, then, if other queries are executed simultaneously, they wouldn't have a retry strategy either, which might explain those errors. – Pralltriller

EfConfig.SuspendExecutionStrategy is stored in the call context so only the methods called from the method that set it will have the strategy suspended, any parallel queries on different threads are unaffected. – Counterproductive

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"

Counterproductive

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

Aletheaalethia