Migrations: Experience when altering memory-optimized is ugly then fails

[rowanmiller]

When you alter an entity to be mapped to a memory-optimized table, this is the migration you get. It's pretty gnarly.

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropPrimaryKey(
                name: "PK_Blogs",
                table: "Blogs");

            migrationBuilder.AlterDatabase()
                .Annotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterTable(
                name: "Blogs")
                .Annotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterColumn<string>(
                name: "Url",
                table: "Blogs",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true)
                .Annotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterColumn<int>(
                name: "BlogId",
                table: "Blogs",
                nullable: false,
                oldClrType: typeof(int))
                .Annotation("SqlServer:MemoryOptimized", true)
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
                .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

            migrationBuilder.AddPrimaryKey(
                name: "PK_Blogs",
                table: "Blogs",
                column: "BlogId")
                .Annotation("SqlServer:Clustered", false)
                .Annotation("SqlServer:MemoryOptimized", true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropPrimaryKey(
                name: "PK_Blogs",
                table: "Blogs")
                .Annotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterDatabase()
                .OldAnnotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterTable(
                name: "Blogs")
                .OldAnnotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterColumn<string>(
                name: "Url",
                table: "Blogs",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true)
                .OldAnnotation("SqlServer:MemoryOptimized", true);

            migrationBuilder.AlterColumn<int>(
                name: "BlogId",
                table: "Blogs",
                nullable: false,
                oldClrType: typeof(int))
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
                .OldAnnotation("SqlServer:MemoryOptimized", true)
                .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

            migrationBuilder.AddPrimaryKey(
                name: "PK_Blogs",
                table: "Blogs",
                column: "BlogId");
        }

Ultimately this operation fails too, so we could look at doing the required table rebuild to swap to memory-optimized. It's a very constrained rebuild, as there are limitations around FKs etc. for memory-optimized tables. We could also just scaffold the drop and create.

System.InvalidOperationException: To set memory-optimized on a table on or off the table needs to be dropped and recreated.
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(AlterTableOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
To set memory-optimized on a table on or off the table needs to be dropped and recreated.

[bricelam]

The MemoryOptimized annotation is copied onto too many Migrations operations. Ideally, it should just be copied from IModel and IEntityType. All the other operations can look at the backing model to determine if they are for a memory-optimized table.

We may also want to allow the annotation to be manually added to each of these operations for when the table is created out-of-band, but this seems low priority.

[bricelam]

Note, you'll still get the calls to DropPrimaryKey() and AddPrimaryKey() because by convention, making the table memory optimized also marks the primary key as non-clustered. When you remove ForSqlServerIsMemoryOptimized(), the primary key also becomes clustered. But the rest of the unrelated operations have been removed.