Transaction for ExecuteSqlCommand in Azure

Question

I am using EF 6 with a Azure Sql database. According to Microsoft, user initiated transactions are not supported (ref: https://msdn.microsoft.com/en-us/data/dn307226#transactions)

Now, with EF 6, ExecuteSqlCommand is wrapped in a transaction by default:

Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. (ref: https://msdn.microsoft.com/en-us/data/dn456843.aspx)

Given my scenario, should I always suppress ExecuteSqlCommand transactional behaviour like this:

context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, @"TRUNCATE TABLE Person;");

Answer 1

This statement that you are referring to applies only to retry strategies:

When you have configured an execution strategy that results in retries ...

The article you linked to is not specific to Azure. Azure SQL Database supports transactions.

Answer 2

Whether or not you want to use TransactionalBehavior.DoNotEnsureTransaction depends on whether you want there to be a transaction during the scope of the command. This is only relevant (as far as I know) if you have multiple T-SQL statements in the batch.

In other words, if your execution strategy has retries and want several statements to be executed within a transaction, they must be all in a single batch like below.

In order for a transaction to span multiple batches, it would have to be created with db.Database.BeginTransaction. It is this explicit BeginTransaction that the document you linked explains is not allowed in combination with retries. The transaction that is created by TransactionalBehavior.EnsureTransaction is allowed regardless of retry policy (because it's fully managed by EF).

// INSERT is rolled back due to error
context.Database.ExecuteSqlCommand(
    TransactionalBehavior.EnsureTransaction,
    @"INSERT INTO MyTable (i) VALUES (1)
    RAISERROR('This exception was intentionally thrown', 16, 1)");

// INSERT is committed
context.Database.ExecuteSqlCommand(
    TransactionalBehavior.DoNotEnsureTransaction,
    @"INSERT INTO MyTable (i) VALUES (1)
    RAISERROR('This exception was intentionally thrown', 16, 1)");

Test program is below.

    private static void Main(string[] args)
    {
        //c:>sqlcmd -E
        //1> create database EFTransaction
        //2> go
        //1> use EFTransaction
        //2> go
        //Changed database context to 'EFTransaction'.
        //1> create table MyTable (i int primary key)
        //2> go
        const string connectionString = "Server=(local);Database=EFTransaction;Integrated Security=SSPI";

        using (DbContext context = new DbContext(connectionString))
        {
            context.Database.ExecuteSqlCommand(
                TransactionalBehavior.DoNotEnsureTransaction,
                @"IF EXISTS (SELECT * FROM sys.tables where name = 'MyTable')
                    DROP TABLE [dbo].[MyTable]
                CREATE TABLE MyTable (i INT PRIMARY KEY)");
        }

        Console.WriteLine("Insert one row."); 
        using (DbContext context = new DbContext(connectionString))
        {
            context.Database.ExecuteSqlCommand(
                TransactionalBehavior.EnsureTransaction,
                @"INSERT INTO MyTable (i) VALUES (0)");
            // Notice that there is no explicit COMMIT command required.
        }

        // Sanity check in a different connection that the row really was committed
        using (DbContext context = new DbContext(connectionString))
        {
            int rows = context.Database.SqlQuery<int>(
                "SELECT COUNT(*) FROM MyTable").Single();
            Console.WriteLine("Rows: {0}", rows); // Rows: 1
        }

        Console.WriteLine();
        Console.WriteLine("Insert one row and then throw an error, all within a transaction.");
        Console.WriteLine("The error should cause the insert to be rolled back, so there should be no new rows");
        using (DbContext context = new DbContext(connectionString))
        {
            try
            {
                context.Database.ExecuteSqlCommand(
                    TransactionalBehavior.EnsureTransaction,
                    @"INSERT INTO MyTable (i) VALUES (1)
                    RAISERROR('This exception was intentionally thrown', 16, 1)");
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }

            int rows = context.Database.SqlQuery<int>(
                "SELECT COUNT(*) FROM MyTable").Single();
            Console.WriteLine("Rows: {0}", rows); // Rows: 1
        }

        Console.WriteLine();
        Console.WriteLine("Insert one row and then throw an error, all within a transaction.");
        Console.WriteLine("The error will not cause the insert to be rolled back, so there should be 1 new row");
        using (DbContext context = new DbContext(connectionString))
        {
            try
            {
                context.Database.ExecuteSqlCommand(
                    TransactionalBehavior.DoNotEnsureTransaction,
                    @"INSERT INTO MyTable (i) VALUES (1)
                    RAISERROR('This exception was intentionally thrown', 16, 1)");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            int rows = context.Database.SqlQuery<int>(
                "SELECT COUNT(*) FROM MyTable").Single();
            Console.WriteLine("Rows: {0}", rows); // Rows: 2
        }
    }