Entity Framework Core MigrationBuilder "ALTER DATABASE statement not allowed within multi-statement transaction."

Question

I am new to Entity Framework Migrations and using Migration Builder in Entity Framework Core 2.2.4 to create raw Sql statements but getting this exception

ALTER DATABASE statement not allowed within multi-statement transaction

Here is my code

public partial class EnableChangeTracking : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"IF(SELECT database_id FROM sys.change_tracking_databases WHERE database_id = DB_ID('xxx')) IS NULL
                               BEGIN
                                    ALTER Database [xxx]
                                    SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)
                               END
                               IF(SELECT [object_id] FROM sys.change_tracking_tables WHERE [object_id]=OBJECT_ID('xxx')) IS NULL
                                AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'xxx')
                               BEGIN
                                    ALTER TABLE [dbo].[xxx] ENABLE CHANGE_TRACKING
                               END");
    }
}

Could anyone suggest how to fix this issue?

Answer 1

You should put each command in a separate .Sql statement inside your migration code.

Also, you should ensure to suppress transaction for each statement, like:

migrationBuilder.Sql("ALTER DATABASE ListManagement SET READ_COMMITTED_SNAPSHOT ON", true);

Sql method signature is outlined here.

suppressTransaction - Boolean

Indicates whether or not transactions will be suppressed while executing the SQL.