Entity Framework Code first self referencing ID inserts wrong entity

Question

I'm having trouble inserting into a table with code first using EF Core 2.0.

I get this exception when calling my context.SaveChanges()

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

Here is my Model:

public class Department 
{
  public int ID { get; set; }
  public string Name { get; set; }
  public int? ParentDepartmentID { get; set; }
  public Department ParentDepartment { get; set; }
}

When I use the SQL Server Profiler I can see that the insert statement of an object which looks like this:

Is clearly trying to insert my ParentDepartment again.

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Departments] ([ID], [Name], [ParentDepartmentID])
VALUES (@p0, @p1, @p2);
',N'@p0 int,@p1 nvarchar(4000),@p2 int',@p0=1,@p1=N'Aalborg',@p2=NULL

My save function which saves the object shown above looks like this:

public string Save()
{
  string result = this.Validate();

  if (result.Equals(""))
  {
    using (LinerContext context = new LinerContext())
    {
      context.Departments.Add(this);
      context.SaveChanges();
    }
  }
  return result;
}

Really hope someone can help - thanks in advance!

From your screenshot it seems like your main object has an id set to (-2147482647). Do you initialize it to min value or something? — Anastasios Selmani, May 02 '18 at 10:05
No I don't I guessed that's the default int value? , should I remove the setter on that property? — Matt Baech, May 02 '18 at 11:48
It seems that there is issue in the way you are saving data. Is the ID set to that large -ve number before you call `context.Departments.Add` on it? — Smit, May 02 '18 at 18:52

score 0 · Answer 1 · answered May 02 '18 at 09:36

0

What you need is probably to change the way you have set your model. For self referencing you should probably do something like this (can refer to this answer for a Fluent API implementation on the model creation too):

public class Department 
{
    public int ID { get;set; }
    public string Name { get;set; }
    public int? ParentDeparmentId { get; set; }

    [ForeignKey("ParentDeparmentId ")]
    public Department ParentDeparment { get; set; }
}

answered May 02 '18 at 09:36

Anastasios Selmani

3,579
3
32
48

I have tried that to no avail, exact same scenario. I cannot count the times I have dropped my DB and recreated with other migrations all different flavors of DataAnnotations and fluent API – Matt Baech May 02 '18 at 09:40

score 0 · Accepted Answer · answered Jul 30 '18 at 07:45

0

Fixed; I just needed to attach the parentdepartment to the dbset

answered Jul 30 '18 at 07:45

Matt Baech

404
11
23

Entity Framework Code first self referencing ID inserts wrong entity

2 Answers2