"Cannot insert explicit value for identity column in table" when inserting object with EF Core

Question

I am trying to insert a new row to my table, using EF Core, SQL Server and C#, but I am having trouble getting EF Core to use the identity column properly.

Here's what I am doing:

I am creating a new object using the Entity Framework generated class (I've included the entity class definition at the end of my post)

EmployeePermissions employee_permission = new EmployeePermissions
{
    FkEmployee = PkEmployee,
    FkPermission = permission_key
};

Then I call db.EmployeePermissions.add(employee_permission), which works on all of my calls where an object comes from [FromBody] <Entity Class> <object_variable> (albeit using other tables).

But here, when I instantiate the class myself, I get this error:

SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Employee_Permissions' when IDENTITY_INSERT is set to OFF.

I don't understand why this is happening — I want it to auto increment the identity column. I used ObjectDumper to see what is getting passed to .Add(), which is as follows:

properties {
    PkEmployeePermissions = 0 [System.Int32] // this is the identity column, of course
    FkEmployee = 31 [System.Int32]
    FkPermission = 6 [System.Int32]
    FkEmployeeNavigation = <null>
    FkPermissionNavigation = <null>
}

I have investigated the other calls which are working fine (the ones where [FromBody] creates an object) and the identity column simply equals 0 in those calls too, so I don't understand what is different here.

Have I misconfigured something in the database? I have double checked in the column properties that the column PkEmployeePermissions is indeed an identity column, so it should be auto incremented.

Here's the Entity class if it helps:

public partial class EmployeePermissions
{
    public int PkEmployeePermissions { get; set; }
    public int FkEmployee { get; set; }
    public int FkPermission { get; set; }

    public Employee FkEmployeeNavigation { get; set; }
    public Permission FkPermissionNavigation { get; set; }
}

Possible duplicate: https://stackoverflow.com/questions/11173562/entity-framework-error-cannot-insert-explicit-value-for-identity-column-in-tabl — Abdullah Dibas, Oct 04 '19 at 20:10
@AbdullahDibas That was indeed the solution! I needed to re-generate the database scaffolding. I must've forgotten to do that after I made the primary key the identity column. — Goulash, Oct 04 '19 at 20:16
Upon further investigation, the exact problem was that the column had `.ValueGeneratedNever()` set inside of the `modelBuilder` in the EF Core database context file. I will add this to an actual answer, so its not a direct duplicate of the other question and instead gives some proper context with how this occurs in EF Core. — Goulash, Oct 04 '19 at 20:31

Goulash · Accepted Answer · 2019-10-07T12:38:31.707

It turned out I had updated the primary key column, PkEmployeePermission to be an identity column and forgotten to re-generate the database scaffolding using EF Core.

The root cause of the problem was that inside of the OnModelCreating() method (in the generated database context file) the field PkEmployeePermission had the method .ValueGeneratedNever() called on it, which meant that even though it was a primary key, EF Core did not automatically generate an incremented value for that column. By commenting out that method in the database context file, the code worked properly.

score 0 · Answer 2 · answered Oct 04 '19 at 20:11

The default convention is for the primary key of a class to be called either Id or (classname)Id - this is not the case in your class.

Also: if you're mapping to an IDENTITY column in SQL Server, you must add a relevant data annotation to the key column.

Try this:

public partial class EmployeePermissions
{
    // add the [Key] annotation to indicate the primary key of the class/table
    [Key]
    // add the [DatabaseGenerated(..)] annotation to indicate an IDENTITY column in the table
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PkEmployeePermissions { get; set; }
    public int FkEmployee { get; set; }
    public int FkPermission { get; set; }

    public Employee FkEmployeeNavigation { get; set; }
    public Permission FkPermissionNavigation { get; set; }
}

Now you should be able to insert those objects, and have them stored properly in your SQL Server table.

None of the EF-generated scaffolding have any annotations for me, and if I add that code it does not resolve to any packages. I'm using EF Core, are you using EF 6 or something like that? I understand the naming conventions of an identity column but I inherited this project and that format is already there and working fine on the other 30 odd tables in the database. — Goulash, Oct 04 '19 at 20:20

"Cannot insert explicit value for identity column in table" when inserting object with EF Core

2 Answers2