EF 6 Code First - Integer primary key - Cannot insert the value NULL into column

Question

I'm trying to update the primary key of my table to be an integer autoincrement Id.

This is my entity:

public class Reservation
{
    public int Id { get; set; }
    public ReservationStatus Status { get; set; }
    public string Name { get; set; }
    public string CustomerName { get; set; }

    ...
}

This is my OnModelCreating method:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        ...

        modelBuilder.Entity<Reservation>()
            .HasKey(e => e.Id)
            .Property(e => e.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        ...
    }

This is the generated migration:

public partial class Reservations_AlterKey_Id_AutoIncrement : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("dbo.Reservations");
        AlterColumn("dbo.Reservations", "Id", c => c.Int(nullable: false, identity: true ));
        AlterColumn("dbo.Reservations", "CustomerTaxCode", c => c.String());
        AddPrimaryKey("dbo.Reservations", "Id");
    }

    public override void Down()
    {
        DropPrimaryKey("dbo.Reservations");
        AlterColumn("dbo.Reservations", "CustomerTaxCode", c => c.String(nullable: false, maxLength: 128));
        AlterColumn("dbo.Reservations", "Id", c => c.Int(nullable: false));
        AddPrimaryKey("dbo.Reservations", new[] { "Id", "DeviceId", "CustomerTaxCode" });
    }
}

When I try to insert a new Reservation (after Update-Database) the following exception is thrown:

Cannot insert the value NULL into column 'Id', table 'xxxxx.dbo.Reservations';

This is what I see in column properties from designer (visual studio 2015):

"Is Identity" is still false.

How can I solve this?

Thanks in advance

Have you tried to add these attributes to the `Id`-property in the `Reservation`-class instead of [fluent](https://msdn.microsoft.com/en-us/data/jj591617.aspx) style: `[DataMember, Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]`. Have you tried to delete the tables to force recreation? — Tim Schmelter, Feb 17 '17 at 12:22
Yes, still the same. I notice that if I delete all migrations,recreate a single migration and then recreating the database Identity Specification is as expected. I would like to avoid to recreate the database because there is already a production version and I won't to erase al data :( Any suggestion? — Androidian, Feb 17 '17 at 12:26
Instead of overloading OnModelCreation please try add the [Key] attribute to your primary key and see if that works. It will help to narrow down the possible issues. — Licht, Feb 17 '17 at 13:09

score 4 · Accepted Answer · edited May 23 '17 at 12:02

4

You can't add an identity to an existing column in SQL Server.

Adding an identity to an existing column

Suggested fix is to create a new column. The best approach is to add a new identity column, create a migration for it then remove the old column and create a migration for that. This way EF wont just try and rename the old column.

edited May 23 '17 at 12:02

Community

1
1

answered Feb 17 '17 at 13:27

Martin Lee

276
2
3

EF 6 Code First - Integer primary key - Cannot insert the value NULL into column

1 Answers1