Migration to change the type of a primary key

[KevinBurton]

In the model I have changed the primary key type from string to long. The initial migration tries to change the column to identity which fails. I have simply changed it to a drop column and re-add the column. This also failed because of the foreign key constraints. Now my Up migration looks like:

            migrationBuilder.DropPrimaryKey("PK_Patient", "Patient");
            migrationBuilder.DropForeignKey("FK_PatientAllergy_Patient_PatientId", "PatientAllergy");
            migrationBuilder.DropForeignKey("FK_MedicationOrder_Patient_PatientId", "MedicationOrder");

            migrationBuilder.DropColumn(
                           name: "PatientId",
                           table: "Patient");
            migrationBuilder.AddColumn<long>(
                name: "PatientId",
                table: "Patient",
                nullable: false);
            migrationBuilder.AddPrimaryKey("PK_Patient", "Patient", "PatientId");

            migrationBuilder.AlterColumn<long>(
                name: "PatientId",
                table: "PatientAllergy",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AlterColumn<long>(
                name: "MRN",
                table: "Patient",
                nullable: false,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AlterColumn<long>(
                name: "PatientId",
                table: "MedicationOrder",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AddForeignKey("FK_PatientAllergy_Patient_PatientId", "PatientAllergy", "PatientId", "Patient");
            migrationBuilder.AddForeignKey("FK_MedicationOrder_Patient_PatientId", "MedicationOrder", "PatientId", "Patient");

But I get the error that

System.ArgumentNullException: Value cannot be null.
Parameter name: identifier

The full exceptions details:

Applying migration '20180424212553_ImplementRepository'.
System.ArgumentNullException: Value cannot be null.
Parameter name: identifier
   at Microsoft.EntityFrameworkCore.Utilities.Check.NotEmpty(String value, String parameterName)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerSqlGenerationHelper.DelimitIdentifier(String identifier)
   at System.Linq.Enumerable.SelectArrayIterator`2.MoveNext()
   at System.String.Join(String separator, IEnumerable`1 values)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.ColumnList(String[] columns)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.ForeignKeyConstraint(AddForeignKeyOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(AddForeignKeyOperation operation, IModel model, MigrationCommandListBuilder builder, Boolean terminate)
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(AddForeignKeyOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.<>c.<.cctor>b__63_1(MigrationsSqlGenerator g, MigrationOperation o, IModel m, MigrationCommandListBuilder b)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c__DisplayClass13_2.<GetMigrationCommandLists>b__2()
   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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Value cannot be null.
Parameter name: identifier

What should be the correct migration?

Steps to reproduce

change the model from string to long
from the PW console in Visual Studio
add-migration
update-database

Further technical details

EF Core version: "Microsoft.EntityFrameworkCore.Tools" Version="2.0.2"
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.6.6

[ajcvickers]

@KevinBurton We're having a bit of trouble figuring out what you are trying to do and what happened. Would it be possible to supply a runnable project/solution or code listing that demonstrates where EF Core did the wrong thing in the scaffolded migration?

[KevinBurton]

I am trying to change the Primary key type. Before it was

    public class Patient
    {
        [Key]
        public string PatientId { get; set; }

I would like to change it to

    public class Patient
    {
        [Key]
        public long PatientId { get; set; }

When the change is made the migration that is generated from the add-migration attempts to make it into a Identity column which when I do 'update-database' I get an error that the column has to be dropped before this can happen. Then I get into the realm of hand editing the generated migration. There are other FK that reference this PK so I just end up changing the migration. I rolled back all of this so the attached project is before I make the change. PocketNurse.zip To see the problem change the type of the property in the model Model/Patient.cs to long (from string) and then do add-migration to see the migration,

the error will become apparent.

Thank you.

[ajcvickers]

@KevinBurton Do you want the key to be an Identity column, or just a normal long column?

[KevinBurton]

@ajcvickers Just a normal long column.

[ajcvickers]

@KevinBurton To configure it as a normal long column, use:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public long PatientId { get; set; }

Migrations should not then try to create an Identity column.

[KevinBurton]

Thank you now I have another database column that I would like to be identity. If I change the property from string to int (in the model) From

        [Key]
        public string CabinetId { get; set; }

to

        [Key]
        public int CabinetId { get; set; }

The generated migration is

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<int>(
                name: "CabinetId",
                table: "CabinetSession",
                nullable: true,
                oldClrType: typeof(string),
                oldNullable: true);

            migrationBuilder.AlterColumn<int>(
                name: "CabinetId",
                table: "Cabinet",
                nullable: false,
                oldClrType: typeof(string))
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<string>(
                name: "CabinetId",
                table: "CabinetSession",
                nullable: true,
                oldClrType: typeof(int),
                oldNullable: true);

            migrationBuilder.AlterColumn<string>(
                name: "CabinetId",
                table: "Cabinet",
                nullable: false,
                oldClrType: typeof(int))
                .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
        }

but update-database gives me a stack trace and an error:

Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using 'C:\Users\Kevin\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.0.2-rtm-10011 initialized 'PocketNurseContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'__EFMigrationsHistory');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'__EFMigrationsHistory');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
Applying migration '20180501221826_ChangeCabinetKeyType'.
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20180501221826_ChangeCabinetKeyType'.
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.<>c.<.cctor>b__63_4(MigrationsSqlGenerator g, MigrationOperation o, IModel m, MigrationCommandListBuilder b)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c__DisplayClass13_2.b__2()
   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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   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.

[ajcvickers]

@KevinBurton 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.