c# - Stored procedure working when calling exec sp and throws with EF6 on SQL Azure

Question

I have this stored procedure

CREATE PROCEDURE [dbo].[sp_RebuildIndexes]
AS
BEGIN
  DECLARE @TableName varchar(255)

  DECLARE TableCursor CURSOR FOR (SELECT
    '[' + IST.TABLE_SCHEMA + '].[' + IST.table_name + ']' AS [TableName]
  FROM INFORMATION_SCHEMA.TABLES IST
  WHERE IST.TABLE_TYPE = 'BASE TABLE')

  OPEN
  TableCursor
  FETCH NEXT FROM TableCursor INTO @TableName
  WHILE @@fetch_status = 0

  BEGIN
    PRINT ('Rebuilding Indexes on ' + @TableName)
  BEGIN TRY
    EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
  END TRY
  BEGIN CATCH
    PRINT ('Cannot do rebuild with Online=On option, taking table ' + @TableName + ' down for doing rebuild')
    EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
  END CATCH
    FETCH NEXT FROM TableCursor INTO @TableName
  END

  CLOSE TableCursor
  DEALLOCATE TableCursor
END

If I execute it with a SQL query

exec [dbo].[sp_RebuildIndexes]

It works fine.

Now calling it from EF6 with this code throws on SQL Azure but works on localdb:

var sqlConnection = (SqlConnection) _context.Database.Connection;
sqlConnection.InfoMessage += (s, m) => messages = m.Message;
_context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]");

The exception:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
[SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Do you have any clue on what is going wrong?

EDIT:

The problem appears only in the case where Indexes can't be rebuilt with ONLINE=ON

EDIT 2: If I use this sp with a SqlConnection object it works.

Answer 1

Finally here is the fix:

I was suspecting a transaction issue, and after a few more investigation here is the problem: If you check here http://msdn.microsoft.com/en-us/data/dn456843

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).

So replacing

_context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]");

With

_context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "exec [dbo].[sp_RebuildIndexes]");

And it works!