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

-1

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; }
}
c#
sql-server
entity-framework
entity-framework-core
asked on Stack Overflow Oct 4, 2019 by Mr. King • edited Oct 4, 2019 by Mr. King

2 Answers

0

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.

answered on Stack Overflow Oct 4, 2019 by marc_s
0

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.

answered on Stack Overflow Oct 5, 2019 by Mr. King • edited Oct 7, 2019 by Mr. King

User contributions licensed under CC BY-SA 3.0