EF Code First giving me error Cannot insert explicit value for identity column in table 'People' when IDENTITY_INSERT is set to OFF. [duplicate]

Question

This question already has answers here:

EF code first: Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF (3 answers)

Closed 2 years ago.

I'm trying out Entity Framework 4's Code First (EF CodeFirst 0.8) and am running into a problem with a simple model that has a 1 <--> 0..1 relationship, between Person and Profile. Here's how they're defined:

public class Person
{
    public int PersonId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime? DOB { get; set; }

    public virtual Profile Profile { get; set; }
}

public class Profile
{
    public int ProfileId { get; set; }
    public int PersonId { get; set; }
    public string DisplayName { get; set; }

    public virtual Person Person { get; set; }
}

The DB context looks like this:

public class BodyDB : DbContext
{
    public DbSet<Person> People { get; set; }   
}

I didn't define a DbSet for Profile because I consider People to be its aggregate root. When I try to add a new Person - even one without a Profile with this code:

public Person Add(Person newPerson)
{
    Person person = _bodyBookEntities.People.Add(newPerson);
    _bodyBookEntities.SaveChanges();
    return person;
}

I get the following error:

Cannot insert explicit value for identity column in table 'People' when IDENTITY_INSERT is set to OFF.

The newPerson object has a 0 for the PersonId property when I call People.Add(). The database tables are People and Profiles. PersonId is the PK of People and is an auto-increment Identity. ProfileId is the PK of Profiles and is an auto-incement Identity. PersonId is a non-null int column of Profiles.

What am I doing wrong? I think I'm adhering to all the EF Code First's convention over configuration rules.

Answer 1

I get the following error: Cannot insert explicit value for identity column in table 'People' when IDENTITY_INSERT is set to OFF.

I think that the IDENTITY_INSERT is the Auto Increment functionality which is off. So, check the field PersonId in the database to see if it is an identity.

Besides, maybe this will fix your problem too.

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int PersonId { get; set; }

Answer 2

This will occur if you perform the following steps:

1. Create a non-identity PK field on a table.
2. Infer the Entity Model from that table.
3. Go back and set the PK identity to true.

The Entity Model and the database are out of sync. Refreshing the model will fix it. I had to do this just yesterday.

Answer 3

If you are using EF Code First, then, in addition to adding the [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] annotation attribute to the model.cs file as others have suggested here, you also need to make the same effective change on the modelMap.cs files (the fluent mapping instructions):

Change from:

this.Property(t => t.id)
   .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

to:

this.Property(t => t.id)
   .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

(I used the EF Power Tools to generate the entity models and the default mapping files, then later turned one Id column into a prmary key column and set it to IDENTITY in Sql Server, therefore, I had to update the attribute and the default mapping file.)

If you don't change it in both places, you'll still get the same error.

Answer 4

You situation reminds me situation I experience with EF Code First when PrimaryKey and ForeignKey are the same column.

There is no direct way to refresh the model, however the same effect can be achieved in 2 steps.

1. Comment out ProfileId in Profile class. Recompile and update database.
2. Uncomment Profile Id, add DatabaseGeneratedAttribute and update database again.

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.None), Key]

This way the generated ProfileId column becomes Key without Identity.

Answer 5

I didn't have this problem until I added a composite key , so once I had 2 primary keys this occurred with EF 6.x.x

On my Key "Id" which has Identity Specification set to true I needed to add

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

Model properties now:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
[Key, Column("Id", Order = 1)]
public int Id { get; set; }

[Key, Column("RanGuid", Order = 2)]
public string RanGuid { get; set; }

Answer 6

If you are using EF core and the fluent interface like me, I've found that the Scaffold-DbContext utility I've used to create the model from an existing db, generate a line for my column like that:

entity.Property(e => e.id).ValueGeneratedNever();

After I've changed the DB adding the IDENTITY attribute to my id, I had to change the row in:

entity.Property(e => e.id).ValueGeneratedOnAdd();

other than adding the [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None), Key] decorator to the id field in my model class.

I'm not even sure if the latter is necessary. After resolved with the former fix, I didn't try to remove it.

Answer 7

For the benefit of searchers: I got this error, but the above fixes did not work. It was due to an error on my part.

On my tables, I have a Guid Primary Key (non-clustered) and an int index.

The error was happening when trying to update the 'Post' with the 'Blog' info as a navigation property. See classes below:

public class Blog
{
    public Guid BlogId { get; set; }

    public int BlogIndex { get; set; }

    // other stuff
}

public class Post
{
    public Guid PostId { get; set; }

    public int PostIndex { get; set; }

    // other stuff

    public Blog Blog { get; set; }
}

The issue was that when I was converting DTO's to models, the BlogId was being changed to a new Guid() (I made an error in the mapping). The resulting error was the same as detailed in this question.

To fix it, I needed to check the data was right when being inserted (it wasn't) and fix the incorrect change of data (in my case, the broken mapping).

Answer 8

Got this error in EF6, looked at the database and everything looked right with Identity Specification set to Yes. I then removed the different migrations and made one new migration from current models and then everything started working. Fastest solution since the application was not live yet and still in development.

Cannot insert explicit value for identity column in table 'Test' when IDENTITY_INSERT is set to OFF.

Answer 9

Here is the solution. Also see the attachment for more help.

Navigate to your EF model ".edmx" file >> Open it >> Now right click on the diagram and choose 'Update Model from Database'.

This will fix it because you made PK the Identity in your DB after you created your EF model.

help to recreate steps stated above

Answer 10

In my case it seems that EF doesn't like other type than INT identity field - mine was a BYTE (TINYINT on the SQL side).

Since I was able to update my project and change it to INT on the SQL, after re-running the Reverse Engineering Code First on VisualStudio, the error has immediately ceased to occur.

Answer 11

In my case it seems that EF doesn't like other type than INT identity field - mine was a BYTE (TINYINT on the SQL side).

I had this error too using PK of tinyint type. It's not that EF doesn't like it, it's seems that, unlike other cases, you have to specify that in your configuration like this:

this.Property(t => t.TableID).HasColumnName("TableID").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);