EF Code First giving me error Cannot insert explicit value for identity column in table 'People' when IDENTITY_INSERT is set to OFF.
Entity FrameworkEntity Framework-4Entity Framework Problem Overview
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.
Entity Framework Solutions
Solution 1 - Entity Framework
>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; }
Solution 2 - Entity Framework
This will occur if you perform the following steps:
- Create a non-identity PK field on a table.
- Infer the Entity Model from that table.
- 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.
Solution 3 - Entity Framework
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.
Solution 4 - Entity Framework
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.
- Comment out ProfileId in Profile class. Recompile and update database.
- Uncomment Profile Id, add DatabaseGeneratedAttribute and update database again.
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.None), Key]
This way the generated ProfileId column becomes Key without Identity.
Solution 5 - Entity Framework
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.
Solution 6 - Entity Framework
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; }
Solution 7 - Entity Framework
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).
Solution 8 - Entity Framework
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.
Solution 9 - Entity Framework
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.
Solution 10 - Entity Framework
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.
Solution 11 - Entity Framework
> 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);