I created a table via entity framework code-first with a primary key set to auto increment, but now I want to remove that auto-incrementing from the column. I've tried doing that with both fluent API:
public class ProductTypeMap: EntityTypeConfiguration<ProductType>
{
public ProductTypeMap()
{
// This is an enum effectively, so we need fixed IDs
Property(x => x.ProductTypeId)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
}
}
And an annotation:
public class ProductType
{
[Required, Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ProductTypeId { get; set; }
public string ProductTypeName { get; set; }
}
And in both cases they produce the same migration code:
public partial class removeproducttypeidentity : DbMigration
{
public override void Up()
{
DropPrimaryKey("dbo.ProductTypes");
AlterColumn("dbo.ProductTypes", "ProductTypeId", c => c.Int(nullable: false));
AddPrimaryKey("dbo.ProductTypes", "ProductTypeId");
}
public override void Down()
{
DropPrimaryKey("dbo.ProductTypes");
AlterColumn("dbo.ProductTypes", "ProductTypeId", c => c.Int(nullable: false, identity: true));
AddPrimaryKey("dbo.ProductTypes", "ProductTypeId");
}
}
However, when I run that migration on the database, the Identity Specification is not removed from the SQL Server 2008 database table?
I also tried explicitly turning off the Identity in the migration as follows, but that didn't do it either:
AlterColumn("dbo.ProductTypes", "ProductTypeId", c => c.Int(nullable: false, identity: false));
Is there another way to tell SQL to remove the Identity?
You can not use ALTER COLUMN
to set whether a column is an identity column (How to alter column to identity(1,1)).
Instead, you have to:
CREATE
TMP_table with columns of original table, but ID column set to identity: falseSET IDENTITY_INSERT [TMP_Table] ON
SET IDENTITY_INSERT [TMP_Table] OFF
DROP
original tableEXECUTE sp_rename
)Tip: change the column in SQL Management Studio and inspect the emitted script (SQL SERVER – Add or Remove Identity Property on Column).
As others have said, EF can't do this natively, although it will create a migration script that makes it look like it has. I've experimented with lots of ways of doing this and I find the easiest way is to:
add-migration [your migration name]
)Up
method in the newly generated migrationSql (@" ");
That should then give you a migration that removes the identity and can be run on other copies of the database successfully.
NB:The Down
method that was generated won't work either because it will be adding the identity back on, which EF also can't do. If you need the Down
method to work create a copy of the SQL you added and adjust it to create the table with the identity again.
The approach above is a quick way of what @Georg Patscheider describes so well in his answer.