Longing to know

I’ve written a number of posts detailing running and working with stored procedures in Entity Framework 6.x. Yesterday, I ran into a weird issue.

Stored procedures are generally executed from within Entity Framework using DbContext.Database.ExecuteSqlCommand(..). Well, I have a stored procedure that I’m running that works fine in SSMS, but was blocking itself once it was run from a code block using EF. This was a bit perplexing to me since the command that was being executed was identical in both platforms.

It turned out that the problem is the way that EF, by default, wraps ExecuteSqlCommand() inside of a transaction. The stored procedure in question is running xp_cmdshell to execute bsp to create a CSV file. The isolation level of the default transaction caused this step in the procedure to be blocked. Very weird.

This is the official word on transactions with EF when using ExecuteSqlCommand:

Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. There are overloads of this method that allow you to override this behavior if you wish. Also in EF6 execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden).

In either case, the isolation level of the transaction is whatever isolation level the database provider considers its default setting. By default, for instance, on SQL Server this is READ COMMITTED.

Fortunately, the overload mentioned allows turning off the creation of a transaction. The overload in question adds a single parameter that takes an enum called TransactionalBehavior. It has two values: EnsureTransaction and DoNotEnsureTransaction. Passing in TransactionalBehavior.DoNotEnsureTransaction, eliminates the creation of the transaction and, eliminating this transaction, fixed my blocking issue.

In my repositories, which have an ExecuteSql() command of their own, my methods now take a boolean indicating whether or not to create the transaction. An example of this method is below:

public void ExecuteSql(string commandText, bool ensureTransaction, dynamic parameters)
{
    TransactionalBehavior transactionalBehavior = ensureTransaction ? TransactionalBehavior.EnsureTransaction : TransactionalBehavior.DoNotEnsureTransaction;
    List<SqlParameter> sqlParams = (parameters != null) ? DynamicToSqlParameters(parameters) : null;

    if (sqlParams != null && sqlParams.Count > 0)
    {
        var executeParams = sqlParams.Select(x => string.Format("@{0}", x.ParameterName)).ToList();
        var executeStr = string.Format("{0} {1}", commandText, string.Join(",", executeParams));
        _dbContext.Database.ExecuteSqlCommand(transactionalBehavior, executeStr, sqlParams.ToArray());
    }
    else
    {
        _dbContext.Database.ExecuteSqlCommand(transactionalBehavior, commandText);
    }
}