Cannot insert explicit value for identity column when identity_insert is set to OFF when auto-increment is already implemented

Question

I have already implemented the auto-increment for database, the identity_insert is set to OFF.

However, when I tried to insert data into the other columns, I get the "Cannot insert explicit value for identity column in table 'Bookmarks' when IDENTITY_INSERT is set to OFF." error. The code below is the table definition:

CREATE TABLE [dbo].[Bookmarks] (
[Id]        INT           IDENTITY (1, 1) NOT NULL,
[carparkId] INT           NULL,
[date]      DATETIME      NULL,
[username]  NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
FOREIGN KEY ([carparkId]) REFERENCES [dbo].[Carparks] ([id])

);

The code below shows the inserting of the data and the error occurs on db.SaveChanges().

    public void Insert(Bookmark obj)
    {
        data.Add(obj);
        db.SaveChanges();
    }

This is the data i am trying to pass in:

 public ActionResult Create([Bind(Include = "carparkId,date,username")] Bookmark bookmark)
    {
        if (ModelState.IsValid)
        {

            bookmarkGateway.Insert(bookmark);
            return RedirectToAction("Index");

        }

Any idea how to solve this error without turning on the identity_insert ? Your help is much appreciated thanks.

Please check if EF is setting up "carparkId" as identity on its generated model. Remember that "Id" is a reserved suffix for primary keys on EF. — JCM
Hi, I have checked the EF. It is not passing "carparkId" as identity, instead, the identity is being setted up as 0 when I ran in debug mode. Also, i tried changing "Id" into "bookmarkId" but when i tried running my program again, there is this exception: "Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'Id'. — purplewind

Alex Lebedev Alex Lebedev · Accepted Answer · 2016-03-15T17:09:14

From the code you posted seems that error in EF mapping.

class BookmarkMap: EntityTypeConfiguration<Bookmark>
{
    public BookmarkMap()
    {
        Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
    }
}

Cannot insert explicit value for identity column when identity_insert is set to OFF when auto-increment is already implemented

1 Answers