How to Add Identity to Primary Key Using Entity Framework Core Migrations?

Question

I have a model class for which I initially omitted the DatabaseGenerationOption.Identity when I created my initial migration. The class is as follows:

 public class Hospital
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int HospitalID { get; set; }

    [Display(Name="Hospital Code")]
    [StringLength(50)]
    public string HospitalCode { get; set; }

    [Display(Name = "Hospital Name")]
    [StringLength(200)]
    public string HospitalName { get; set; }


}

When I run dotnet ef database update, I receive the following error:

Executed DbCommand (60ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT OBJECT_ID(N'__EFMigrationsHistory');
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT OBJECT_ID(N'__EFMigrationsHistory');
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
Applying migration '20170423214618_UpdateHospitalIdentity'.
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(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.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.

I see that the migration created an AlterColumn method as follows:

public partial class UpdateHospitalIdentity : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<int>(
            name: "HospitalID",
            table: "Hospital",
            nullable: false,
            oldClrType: typeof(int))
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<int>(
            name: "HospitalID",
            table: "Hospital",
            nullable: false,
            oldClrType: typeof(int))
            .OldAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
    }
}

Update: I tried executing dotnet ef migrations removewith the intent to remove the InitialCreate migration. However, since I ran update database after the InitialCreate migration, I received the following:

Build succeeded.
0 Warning(s)
0 Error(s)

Time Elapsed 00:00:04.18
Executed DbCommand (55ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT OBJECT_ID(N'__EFMigrationsHistory');
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
The migration '20170419050810_InitialCreate' has already been applied to the 
database. Unapply it and try again. If the migration has been applied to 
other databases, consider reverting its changes using a new migration.

How do I drop the table in EF Core migrations and re-create it using the intended identity constraint?

Try with `dotnet ef migrations remove` to remove the last migration. I think this annotation was automatically generated for you because .OldAnnotation is the same. If I'm right you don't have to do anything. — Konrad, Apr 25 '17 at 17:35
Should I do this for the two migrations I've created? That is, should I remove the InitialCreate and the UpdateHospitalIdentity migrations? — SidC, Apr 25 '17 at 17:40
Yes, that would work if you don't have anything important in your database. You should check in the database how does the table look like right now. It's possible the annotation was added automatically by EF core. One time I was creating a model I got an error because ID column was required and I had to either add Id property or use data annotation. Sometimes it just looks at the naming of your fields and adds those things automatically. — Konrad, Apr 25 '17 at 17:44
http://stackoverflow.com/questions/38192450/how-to-unapply-a-migration-in-asp-net-core-with-ef-core — Konrad, Apr 25 '17 at 19:55

How to Add Identity to Primary Key Using Entity Framework Core Migrations?

0 Answers0