Entity Framework error: Cannot insert explicit value for identity column in table

Question

I'm getting this error on EF.

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

The column on the Db is identity increment and on the EF design file, StoreGeneratedPattern is identity as well. Seems like EF is trying to insert 0 every time I try to save.

Some suggestions says ID is reserved on tables or drop the table and rerun the scripts.

Any ideas?

Here's some code:

GroupMember groupMember = new GroupMember();
            groupMember.GroupId = group.Id;
            groupMember.UserId = (new UserId(group.Owner));
            //groupMember.Id = _groupContext.GroupMembers.Count();
            group.GroupMembers.Add(groupMember);

            _groupContext.SaveChanges();

Answer 1

I was also having the same error, where I am inheriting key fro, BaseEntity class. To resolve this just add

'[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]'

on the BaseEntity class or wherever your primary key is

Answer 2

You might have an .AsNoTracking() somewhere.

To solve the error, here is an example:

Given Student and Course entities:

class Student
{
    public int Id { get; set; }
}

class Course
{
    public int Id { get; set; }
    public int StudentId { get; set; }
    public Student Student { get; set; }
}

And given this GetStudent() method which uses .AsNoTracking();

Student GetStudent(int studentId)
{
    return await dbContext.Students
        .AsNoTracking()
        .FirstOrDefault(e => e.Id == studentId);
}

If you are creating a new course, use the property StudentId instead of Student to set the relationship between the student and the course:

var student = GetStudent(studentId);
// check student is not null
var course = new Course
{
    StudentId = student.Id
};
await repo.CreateAsync(course);

Don't do this:

var student = GetStudent(studentId);
// check student is not null
var course = new Course
{
    Student = student
};
await repo.CreateAsync(course);

Answer 3

Entity Framework Core 8:

As previously stated, this error means you are trying to assign a value explicitly to a column where the database automatically assigns it.

This is protection designed to keep you from screwing up the ID column and all the internal workings that happen when the database generates the ID. Use with caution. I have only used this method when seeding a new database with a few default entries, so have not seen the effects of doing this to an established database. I'm just simplifying what I gathered from a bunch of other posts in other places, for noobs like myself who might struggle to put it all together.

You can toggle off the identity protection temporarily while you make your edit like this:

using EntityFrameworkCore;    

//create your new object to add into your table (or you can get from a diff database if migrating)
YourObject yourObjectToAdd = new YourObject();
yourObjectToAdd.Id = 1; //assuming your identity column is named "Id" - if not, change to match your identity column 
//... I'm assuming you'll add other data to your object here

//then get the database context (if you don't already have it handy)
//this was my code in my .net 8.0 MVC project, your mileage may vary
var db = scope.ServiceProvider.GetRequiredService<YourDBContext>();

//not a bad idea to check that the database is properly created if you're starting from scratch (if not you can remove this)
db.Database.EnsureCreated();

//wrap the process in a single transaction or it will discard your identity column data assuming that the db will generate it!!!
using (var transaction = db.Database.BeginTransaction())
{
     db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[YourTableNameHere] ON");

     db.YourTableNameHere.Add(yourObjectToAdd);
     db.SaveChanges();

     db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[YourTableNameHere] OFF");

     transaction.Commit();
}

Note: Because you are hard-coding the name of the database table in these SQL statements, if you rename your tables later (which can happen in a code-first environment if you rename your class), this code will break. This is a good reason to consider not using this approach for long-term production code rather than a one-time utility type usage.

Answer 4

Well, You need give a value to ID, for example for the object Auto, just you should VarAuto.Id = 0;

After that you could do it something like this =>

using( MyContext db = new MyContext()){

db.Autos.Add(VarAuto);
db.SaveChanges();
}

That is the solution just give value to id, EF could be recognize the identity value in the table.

Answer 5

If you have something like "entity.Property(e => e.Id).ValueGeneratedNever();" for your entity in your DbContext, remove it.

Answer 6

Add this line in order to allow the Id column to receive 1,2,3 and 4 values instead of being auto-numbered.

Sql("SET IDENTITY_INSERT MembershipTypes ON")

Answer 7

If you don't want to use EF core's auto-generating primary key values feature, you can turn it off. You can add your data to the primary key It should resolve the error - Set Identity Insert off

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.None)]

public int StudentId { get; set; }

Setting Database Generation option to None helped me. You can find more about it here- https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations

Answer 8

EF Code first: Because of an auto-increment PK 'id' field AND a guid column, design like this:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid FileToken { get; set; }

there was a duplicate identity. I changed it to:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[DefaultValue("newid()")]
public Guid FileToken { get; set; }

and the problem went away.

Answer 9

I'm using DB first and the table has identity column. I didn't use the db-scaffolding to generate this, I copied it from another entity and by mistake I took this property with.

So

Try to check the DBContext Class. I got this error, and the issue was with this property ".ValueGeneratedNever()" I have just removed it and it works fine,

modelBuilder.Entity<TableName>(entity =>
        {
            entity.Property(e => e.Id)
                //.ValueGeneratedNever()
                .HasColumnName("ID");
        });

Answer 10

I solved this by removing primary key in model from inserting data. because primary key auto increment.

var book = new Book
   {
   //   Id = 1,  //Don't need to write this
        Genre = "Technology",
        Author = "Charles Petzold",
        Title = "Programming Windows 5th Edition",
        Price = 30,
        Publisher = "Microsoft Press"
    };

  _unitOfWork.Books.Add(book);
       

Answer 11

See intercepting Entity Insert for generated always columns like StartTime and EndTime columns on history tables, rowversion columns as well.

Answer 12

I encountered the same problem and error message in my AspNetCore 2.x application. The only way I could solve it was by removing this line in the ModelBuilder.Entity method of the DbContext class:

// remove: entity.Property(e => e.Id).ValueGeneratedNever();

Answer 13

I had this issue in my app; and got fixed it changing the property "StoredGeneratedPattern" of the id field to Identity.

So, Go to the model; look up for the table; click on propierties of the primary key fiel; and change the property.

Answer 14

Try this:

using System.ComponentModel.DataAnnotations.Schema;
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public decimal Identity_Col { get; set; }

The Entity Framework class file adds these lines of code to the Identity column.

Answer 15

Put these attribs on top of the property which is identity:

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

Answer 16

In EF 6, there is a property of the field/column in your model for doing this: StoreGeneratedPattern.

Set this to "Identity" in the property dropdown list.

(I don't know about EF 4. The above answer, using IsDbGenerated, seems to be for EF 4.)

And this corresponds in the underlying XML to an attribute to the element:

<Property Name="MyTableId" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

--but you don't need to deal with the XML manually, since you can use the designer.

How this gets messed up isn't clear. I had the problem even after refreshing my model from the database. Perhaps it gets confused if you set the PK on the table, or change its name, after you have already generated the model. (I am using the table/database-first approach, not code first.)

You can't use the above approach of putting the C# attribute on the entity code, because in this situation the entity code is generated by EF. EF is supposed to understand ("by itself") that the field is an identity.

Answer 17

I have run into this before. This error means you are trying to assign a value explicitly to a column where the database automatically assigns it.

Suggestion: Update your edmx file to reflect any changes you may have made in the database. If the database automatically assigns the value, you should see the "IsDbGenerated=true" attribute in your designer file under that property. If it's not there, you can add it manually.