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

.net-core c# entity-framework-core entity-framework-core-migrations

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?

1
0
8/2/2019 11:14:44 AM

Popular Answer

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.

0
8/16/2019 12:55:20 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow