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