SqlServer Migrations: Scripts do not fail on failure

Start by reproducing the failure with dotnet ef migrations script and the generated SQL Server migration script shown in the issue. Trace the SQL Server migration-script generation entry point and verify that an error stops later batches, rolls back prior migration changes, and does not record the failed migration in __EFMigrationsHistory.

One of the new features added in 5.0-rc1 was that transactions are now used in migrations, however if there is a failure in your migration, the transaction does not work properly because of the use of GO batch terminators. The transaction will not rollback changes prior to the error and will continue executing after the error'ed batch and will proceed to commit the successful batches of the failed migration.

In short, the current behaviors of suppressTransaction = true and suppressTransaction = false are pretty much the same. While a transaction is added to the script, it doesn't add any protection and the results of executing the script are the same.

Steps to reproduce

1. Create a new project with the following DbContext setup:

// Startup.cs
services.AddDbContext<ApplicationContext>(x => {
    x.UseSqlServer(@"Server=(localdb)\mssqllocaldb; Database=ef; Trusted_Connection=True; MultipleActiveResultSets=true");
});

public class ApplicationContext : DbContext {
    public ApplicationContext(DbContextOptions<ApplicationContext> options) : base(options) {}

    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder model) {
        model.Entity<Blog>(x => {
            x.Property(x => x.Name).HasMaxLength(10);
            x.HasData(new Blog { BlogId = 1, Name = "Blog 1" });
            x.HasData(new Blog { BlogId = 2, Name = "Blog 2 - Name is too long" });
        });
    }
}

public class Blog {
    public int BlogId { get; set; }
    public string Name { get; set; }
}

2. Create a migration:

dotnet migrations add Initial

public partial class Initial : Migration {
    protected override void Up(MigrationBuilder migrationBuilder) {
        migrationBuilder.CreateTable(
            name: "Blogs",
            columns: table => new {
                BlogId = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(type: "nvarchar(10)", maxLength: 10, nullable: true)
            },
            constraints: table => {
                table.PrimaryKey("PK_Blogs", x => x.BlogId);
            });

        migrationBuilder.InsertData(
            table: "Blogs",
            columns: new[] { "BlogId", "Name" },
            values: new object[] { 1, "Blog 1" });

        migrationBuilder.InsertData(
            table: "Blogs",
            columns: new[] { "BlogId", "Name" },
            values: new object[] { 2, "Blog 2 - Name is too long" });
    }
}

3. Create a migration script:

dotnet ef migrations script -o "migrate.sql"

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [Blogs] (
    [BlogId] int NOT NULL IDENTITY,
    [Name] nvarchar(10) NULL,
    CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
);
GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
    SET IDENTITY_INSERT [Blogs] ON;
INSERT INTO [Blogs] ([BlogId], [Name])
VALUES (1, N'Blog 1');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
    SET IDENTITY_INSERT [Blogs] OFF;
GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
    SET IDENTITY_INSERT [Blogs] ON;
INSERT INTO [Blogs] ([BlogId], [Name])
VALUES (2, N'Blog 2 - Name is too long');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
    SET IDENTITY_INSERT [Blogs] OFF;
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200919002238_Initial', N'5.0.0-rc.1.20451.13');
GO

COMMIT;
GO

4. Run the migration.sql script in an empty SQL database and notice that you get the following error:

(1 row affected)
Msg 8152, Level 16, State 13, Line 31
String or binary data would be truncated.
The statement has been terminated.

(1 row affected)

Completion time: 2020-09-18T17:29:20.1685190-07:00

The script continued to run even though there was an error and inserted the first blog entry and the migration entry but not the second blog entry.

Possible Solutions

You can see a current working solution for EF Core 3.1 here: https://github.com/dotnet/efcore/issues/7681#issuecomment-602878706

The basic details of that solution are:

1. Execute SET XACT_ABORT ON; at the beginning of the script
2. Only execute the batch IF XACT_STATE() = 1
3. Only commit a transaction IF XACT_STATE() = 1

Something to be aware of, using XACT_STATE() within an IF statement with more than 1 condition causes it to not work properly so you will need to make a variable to capture the state. See: https://github.com/dotnet/efcore/issues/7681#issuecomment-613017864

Following these steps, the migration would look something like this:

SET XACT_ABORT ON;
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

IF XACT_STATE() = 1
BEGIN
    CREATE TABLE [Blogs] (
        [BlogId] int NOT NULL IDENTITY,
        [Name] nvarchar(10) NULL,
        CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
    );
END
GO

IF XACT_STATE() = 1
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] ON;
    INSERT INTO [Blogs] ([BlogId], [Name])
    VALUES (1, N'Blog 1');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] OFF;
END
GO

IF XACT_STATE() = 1
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] ON;
    INSERT INTO [Blogs] ([BlogId], [Name])
    VALUES (2, N'Blog 2 - Name is too long');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Name') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] OFF;
END
GO

IF XACT_STATE() = 1
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20200919002238_Initial', N'5.0.0-rc.1.20451.13');
END
GO

IF XACT_STATE() = 1
BEGIN
    COMMIT;
END
GO

Which results in the __EFMigrationsHistory table being created, but it stays empty and all the rest of the script is rolled back.

Further technical details

EF Core version: 5.0.0-rc.1.20451.13
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.8.0 Preview 3.0