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 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.

Answer 2

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 3

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

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

Answer 4

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 5

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 6

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 7

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 8

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

Answer 9

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 10

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.

Just Try.

Answer 11

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 12

Note: a moderator deleted this answer as a duplicate and left my other answer up, on a question with only the sql-server tag (which was the first question I arrived at from google). Since this question has the entity framework tag, posting the answer again here.

This is for EntityFramework Core 3.1.22. Using the wrong property to specify a foreign key causes Entity Framework to demote the primary key to ... something else. Entity Framework will then always attempt to insert an explicit value, which throws a database exception because it can't insert the value it's been told is a primary key and shouldn't be inserted.

Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'
Inner Exception:
SqlException: Cannot insert explicit value for identity column in table 'FOO' when IDENTITY_INSERT is set to OFF.

Code example. We have a 1-to-1 class mapping:

    public class Foo /* child */
    {
        public int FooPrimaryKey { get; set; }
        public int BarPrimaryKey { get; set; }
        public virtual Bar PropertyBar {get; set; }
    }

    public class Bar
    {
        public int BarPrimaryKey { get; set; }
        public virtual Foo PropertyFoo {get; set; }
    }

    modelBuilder.Entity<Foo>(entity =>
    {
        entity.HasKey(e => e.FooPrimaryKey);

        entity.ToTable("FOO", "dbo");
        
        entity.HasOne(d => d.PropertyBar)
            .WithOne(x => x.PropertyFoo)
            // wrong, this throws the above exception
            .HasForeignKey<Bar>(x => x.BarPrimaryKey);
    });

The foreign key should instead be (same key, different type):

    .HasForeignKey<Foo>(x => x.BarPrimaryKey);

Answer 13

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 14

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 15

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