Identity Column not being modified by Entity framework

Question

The relevant Entity is as follows:

public class FieldBookingDateRange : ITimeStamps, ICreatedBy, ISoftDeletable
{
    [Key, DatabaseGenerated( DatabaseGeneratedOption.None )]
    public int FieldBookingDateRangeID { get; set; }

    [Required]
    public virtual FieldBooking FieldBooking { get; set; }

    public virtual DateRange DateRange { get; set; }

    ....
}

I have a database column that is currently set to Identity: true.

If i run the following auto generated migration on it (This should be setting the column to false on identity):

public partial class RemovedDBGen_FieldBookingDateRange : DbMigration
{
    public override void Up()
    {
        DropIndex("dbo.FieldBookingDateRanges", new[] { "FieldBookingDateRangeID" });
        DropPrimaryKey("dbo.FieldBookingDateRanges");
        AlterColumn("dbo.FieldBookingDateRanges", "FieldBookingDateRangeID", c => c.Int(nullable: false, identity: false));
        AddPrimaryKey("dbo.FieldBookingDateRanges", "FieldBookingDateRangeID");
        CreateIndex("dbo.FieldBookingDateRanges", "FieldBookingDateRangeID");
    }

    public override void Down()
    {
        DropIndex("dbo.FieldBookingDateRanges", new[] { "FieldBookingDateRangeID" });
        DropPrimaryKey("dbo.FieldBookingDateRanges");
        AlterColumn("dbo.FieldBookingDateRanges", "FieldBookingDateRangeID", c => c.Int(nullable: false, identity: true));
        AddPrimaryKey("dbo.FieldBookingDateRanges", "FieldBookingDateRangeID");
        CreateIndex("dbo.FieldBookingDateRanges", "FieldBookingDateRangeID");
    }
}

It doesn't do anything... But doesn't fail.

Any ideas why this isn't working?

SQL -Verbose output

IF EXISTS (
    SELECT name FROM sys.indexes 
    WHERE 
        name = N'IX_FieldBookingDateRangeID' AND 
        object_id = object_id(N'[dbo].[FieldBookingDateRanges]', N'U')
)
DROP INDEX [IX_FieldBookingDateRangeID] ON [dbo].[FieldBookingDateRanges]
ALTER TABLE [dbo].[FieldBookingDateRanges] DROP CONSTRAINT [PK_dbo.FieldBookingDateRanges]
ALTER TABLE [dbo].[FieldBookingDateRanges] ALTER COLUMN [FieldBookingDateRangeID] [int] NOT NULL
ALTER TABLE [dbo].[FieldBookingDateRanges] ADD CONSTRAINT [PK_dbo.FieldBookingDateRanges] PRIMARY KEY ([FieldBookingDateRangeID])
CREATE INDEX [IX_FieldBookingDateRangeID] ON [dbo].[FieldBookingDateRanges]([FieldBookingDateRangeID])

I've done that for you... Going to check over it myself too. — Jimmyt1988, Apr 28 '15 at 22:59
It seems i have to completely drop the table in order to modify it :S ? — Jimmyt1988, Apr 28 '15 at 23:08
It might be a limitation of SQL Server. I remember I had to do that a while ago, but can't find any documentation to that effect. — trailmax, Apr 28 '15 at 23:24
Some explanation of why you have the problem over here: http://stackoverflow.com/a/18917348/150342 — Colin, Apr 29 '15 at 09:52

score 1 · Accepted Answer · edited May 23 '17 at 12:25

I think you can't remove the identity property from a column once it's set. However you might be able to modify the automatically generated migration to implement a workaround.

You can find some examples here:

Remove Identity from a column in a table
If you need to keep the data, but remove the IDENTITY column, you will need to:
- Create a new column
- Transfer the data from the existing IDENTITY column to the new column
- Drop the existing IDENTITY column.
https://dba.stackexchange.com/questions/87420/how-to-remove-identity-specification-for-multiple-tables

Identity Column not being modified by Entity framework

1 Answers1