Expectation for creating a migration that removes an identity

[lcrumbling]

Let's say that you have an autonumber column. You add an annotation to change it into a number:

[DatabaseGenerated(DatabaseGeneratedOption.None)]

Would you expect the code change to generate a migration that works, or one that throws an exception?

Currently, I'm getting an exception when I try to run the generated migration:

System.InvalidOperationException: 'To change the IDENTITY property of a column, the column needs to be dropped and recreated.'

My expectation was that it would have created a migration that performed the drop, and recreate. Yes, I realize that's a lot, so perhaps this is expected behavior/a known issue.

[lauxjpn]

@lcrumbling It is a reasonable expectation. Currently, the migration support is a bit rudimentary, but we add features like this one if they are needed. So we will implement this feature.

As a workaround in the meantime, create a new column, UPDATE it with the values from the original column, drop the original column and finally rename the new column (with some Jet provider specific SQL: ALTER TABLE `YourTable` RENAME COLUMN `YouNewColumn` TO `YourOriginalColumn`).

[ChrisJollyAU]

Also, being the identity column, it will affect any foreign key relationships as well. You may have to drop any relationships first, do the change, the recreate the relationships. That will make things a bit more complex

[ChrisJollyAU]

Oddly enough I ended up with this scenario against a SQL Azure database this week and got exactly the same error. So even the normal sql server migrations behaves like this. Can probably close this as expected behaviour