Efcore: Migrations not being generated correctly when changing the primary key type

[craigahanson]

I am using aspnet identity and I'm trying to change the primary key from nvarchar(450) to bigint. I have made the necessary changes in identity and the migration has been generated successfully (see below) but errors when I execute it with the following error message..

_System.InvalidOperationException: To change the IDENTITY property of a column, the column needs to be dropped and recreated.
at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(AlterColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList1 operations, IModel model) at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList1 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 change the IDENTITY property of a column, the column needs to be dropped and recreated._

Steps to reproduce

public class MyIdentity : IdentityUser<long>
{
}

public class MyContext : IdentityDbContext<MyIdentity, IdentityRole<long>, long>
{
}

Generated migration

public partial class ChangeIdentityKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<long>(
            name: "Id",
            table: "AspNetUsers",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string))
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

        migrationBuilder.AlterColumn<long>(
            name: "UserId",
            table: "AspNetUserTokens",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<long>(
            name: "RoleId",
            table: "AspNetUserRoles",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<long>(
            name: "UserId",
            table: "AspNetUserRoles",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<long>(
            name: "UserId",
            table: "AspNetUserLogins",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<long>(
            name: "UserId",
            table: "AspNetUserClaims",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<long>(
            name: "Id",
            table: "AspNetRoles",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string))
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

        migrationBuilder.AlterColumn<long>(
            name: "RoleId",
            table: "AspNetRoleClaims",
            type: "bigint",
            nullable: false,
            oldClrType: typeof(string));
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "Id",
            table: "AspNetUsers",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint")
            .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "AspNetUserTokens",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint");

        migrationBuilder.AlterColumn<string>(
            name: "RoleId",
            table: "AspNetUserRoles",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint");

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "AspNetUserRoles",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint");

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "AspNetUserLogins",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint");

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "AspNetUserClaims",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint");

        migrationBuilder.AlterColumn<string>(
            name: "Id",
            table: "AspNetRoles",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint")
            .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

        migrationBuilder.AlterColumn<string>(
            name: "RoleId",
            table: "AspNetRoleClaims",
            nullable: false,
            oldClrType: typeof(long),
            oldType: "bigint");
    }
}

Further technical details

EF Core version: 2.0.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio Enterprise 2017 Version 15.3.4

[ajcvickers]

@craigahanson As the message says, this change requires the table to be re-built, which is something that Migrations doesn't yet support--it is being tracked by #329. To make this kind of change you will have to manually drop and re-create the table. This can be done by writing your own Drop/Create methods in the Migration, or by using raw SQL commands against the database.

[BryantVail]

this actually says, "Column" Needs to be dropped & re-created, not "table"

[ntimmerman]

LOL, what a joke.

@craigahanson As the message says, this change requires the table to be re-built, which is something that Migrations doesn't yet support--it is being tracked by #329. To make this kind of change you will have to manually drop and re-create the table. This can be done by writing your own Drop/Create methods in the Migration, or by using raw SQL commands against the database.

[slcoder]

@craigahanson did you find the solution?

[Strypper]

@slcoder got the same issue here, with this it's impossible to create many to many relationship with the Identity related

[Strypper]

@slcoder I finally did. You guys do the following things

• Drop the whole database

• Reinitialize the migration

• Update the database
  there's not thing wrong in your class models. SQL Server just forced you to recreate the whole thing

[slcoder]

@slcoder I finally did. You guys do the following things

• Drop the whole database
• Reinitialize the migration
• Update the database
  there's not thing wrong in your class models. SQL Server just forced you to recreate the whole thing

Hi @Strypper,
Thank for the reply, Ultimately I also did the same way dropping database and recreate it. However, that is fine since this project is still on the initial stage, but need to find a solution for a longer run since we cant dropping database whenever we do the model changes.

[Strypper]

@slcoder I finally did. You guys do the following things

• Drop the whole database
• Reinitialize the migration
• Update the database
  there's not thing wrong in your class models. SQL Server just forced you to recreate the whole thing

Hi @Strypper,
Thank for the reply, Ultimately I also did the same way dropping database and recreate it. However, that is fine since this project is still on the initial stage, but need to find a solution for a longer run since we cant dropping database whenever we do the model changes.

Did you get the same error when you recreate it by migration ?

[slcoder]

@slcoder I finally did. You guys do the following things

• Drop the whole database
• Reinitialize the migration
• Update the database
  there's not thing wrong in your class models. SQL Server just forced you to recreate the whole thing

Hi @Strypper,
Thank for the reply, Ultimately I also did the same way dropping database and recreate it. However, that is fine since this project is still on the initial stage, but need to find a solution for a longer run since we cant dropping database whenever we do the model changes.

Did you get the same error when you recreate it by migration ?

Yes.
Another solution what I did was, Remove that generated Identity schema from generated migration file (Up, Down methods), update the database with only necessary changes I want.

Can you try below with your configuration file
builder.Property(c => c.YourPrimaryId)
.ValueGeneratedNever();

[Strypper]

@slcoder I finally did. You guys do the following things

• Drop the whole database
• Reinitialize the migration
• Update the database
  there's not thing wrong in your class models. SQL Server just forced you to recreate the whole thing

Hi @Strypper,
Thank for the reply, Ultimately I also did the same way dropping database and recreate it. However, that is fine since this project is still on the initial stage, but need to find a solution for a longer run since we cant dropping database whenever we do the model changes.

Did you get the same error when you recreate it by migration ?

Yes.
Another solution what I did was, Remove that generated Identity schema from generated migration file (Up, Down methods), update the database with only necessary changes I want.

Just to make sure that you apply the right changes:

1. Inheritate IdentityUser in the User Model
2. Inheritance IdentityDbContext, int> in AppDbContext
3. services.AddIdentity> in the startup.cs
   If it still show the same error, I'm confident enough to tell you need to drop the whole database + migration and add migration again. No need to tweek around the generated Migration cs file