Cannot insert explicit value for identity column i

Question

I have the below error when I execute the following script. What is the error about, and how it can be resolved?

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)

Error:

Server: Msg 544, Level 16, State 1, Line 1

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

Answer 1

don't put value to OperationID because it will be automatically generated. try this:

Insert table(OpDescription,FilterID) values ('Hierachy Update',1)

Answer 2

In your entity for that table, add the DatabaseGenerated attribute above the column for which identity insert is set:

Example:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int TaskId { get; set; }

Answer 3

Well I solved mine pretty easy, Check that your Primary Key are the same name with your classes where the only difference is that your primary key have 'ID' appended to it or specify [Key] on primary keys that are not related to how the class is named.

Answer 4

you can simply use This statement for example if your table name is School. Before insertion make sure identity_insert is set to ON and after insert query turn identity_insert OFF

SET IDENTITY_INSERT School ON
/*
  insert query
  enter code here
*/
SET IDENTITY_INSERT School OFF

Answer 5

If you are using liquibase to update your SQL Server, you are likely trying to insert a record key into an autoIncrement field. By removing the column from the insert, your script should run.

<changeSet id="CREATE_GROUP_TABLE" >
    <createTable tableName="GROUP_D">
        <column name="GROUP_ID" type="INTEGER" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="INSERT_UNKNOWN_GROUP" >
    <insert tableName="GROUP_D">    

        <column name="GROUP_ID" valueNumeric="-1"/>
 ...
    </insert>
</changeSet>

Answer 6

The problem raised from using non-typed DBContext or DBSet if you using Interface and implement method of savechanges in a generic way

If this is your case I propose to strongly typed DBContex for example

MyDBContext.MyEntity.Add(mynewObject)

then .Savechanges will work