SqlServer Migrations: Rebuild column when IDENTITY changes

[stuartleeks]

Functional Impact

Changing a property to become an identity throws exception on migration

Minimal repro steps

1. Add an entity with an integer non-key property
2. add and apply a migration to create the table for the entity
3. Configure the property to be the key and an identity field
4. Add and apply a migration. At this point you will get the SqlException

Expected result

The migration is detecting that the column in the table isn't and identity in the database but needs to be to satisfy the model. Rather than generating and executing SQL that isn't valid, it would be more useful to detect the error and display a meaningful message to the user indicating which entity and property are causing the issue

Actual result

Migration fails with SqlException "Incorrect syntax near the keyword 'IDENTITY'."

[bricelam]

This is related to #2070

[bricelam]

Enabling this scenario, would require a table rebuild (issue #329). Once #2070 is fixed, EF7 behavior will match EF6. I'm closing this for now with a note on #329 to consider doing it in the future.

[bricelam]

(Re-opening to track this separately from #329)

[bricelam]

Additional duplicates: #3993 #7444

[ajcvickers]

Workaround from #20549

Rebuild the table:

1. Add a new table with IDENTITY on the column
2. Copy data from the old table to the new table (with IDENTITY_INSERT ON)
3. Drop referencing constraints and indexes
4. Drop the old table
5. Rename the new table back to its original name
6. Re-create referencing constraints and indexes

The easiest way might be to just generate a script using SSMS and paste it into your migration:

// UNDONE: Not supported by SQL Server
//migrationBuilder.AlterColumn<int>(
//    name: "Id",
//    table: "Areas",
//    nullable: false,
//    oldClrType: typeof(int),
//    oldType: "int")
//    .Annotation("SqlServer:Identity", "1, 1");
migrationBuilder.Sql(@"
    CREATE TABLE [dbo].[tmp_ms_xx_...
    SET IDENTITY_INSERT [dbo].[tmp_ms_xx_...
    INSERT INTO [dbo].[tmp_ms_xx_...
    SET IDENTITY_INSERT [dbo].[tmp_ms_xx_...
    DROP TABLE [dbo].[...
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_...
    ALTER TABLE [dbo].[...
        ADD FOREIGN KEY ([...
");

[emreerkoca]

Another easy solution:

1. Delete all migrations.
2. Delete database
3. Add migration
4. Update database :)

[bricelam]

[TAGC]

This would be nice to have. I'm having trouble trying to apply a migration due to this.

[mamaly12]

Hi everybody,
Has this issue been fixed yet?
I still facing with this error: "To change the IDENTITY property of a column, the column needs to be dropped and recreated."

[roji]

@mamaly12 this issue is in the backlog, so not planned for work at the moment. Please read the above comments for workarounds.

[mamaly12]

@mamaly12 this issue is in the backlog, so not planned for work at the moment. Please read the above comments for workarounds.

Thank you @roji, but I think deleting a database or a table with big data is a very risky task, since you have to get a backup of everything and make sure to import all of data again.

[ajcvickers]

@mamaly12 That's precisely why this is not implemented. The only way to do this is to drop the table and rebuild it. That's a risky thing to do automatically, especially when metadata about the table may not be complete or accurate.

[roji]

Note the additional case of the identity seed/increment changing (#28377); although there's a way to change these without recreating the column, but apparently that doesn't persist across table truncations.