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

Question

For my API I'm using Entity Framework Core with code first migrations. I've created some relations which are working fine. Now, I've added another relation (one to many) and suddenly I'm slapped around the ears with this error:

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

Offcourse, I must be doing something wrong but I just can't figure out what. I've come across more questions like this where the answer was "set IDENTITY_INSERT to ON" but that doesn't work for me since EF is handling everything.

My Company class which can belong to a Group:

public class Company
{
  // Primary key
  public int Id { get; set; }

  // The optional Id of a Group
  public int? GroupID { get; set; }

...

}

And the Group class:

public class Group
{
  // Primary key
  public int Id { get; set; }

  // Name of the Group
  public string Name { get; set; }

  // List of Companies in this group
  public IEnumerable<Company> Companies { get; set; }
}

The code used for handling the POST:

// POST api/groups
[HttpPost]
public async Task<IActionResult> Post([FromBody] Group group)
{
  try
  {
    if (ModelState.IsValid)
    {
      _context.Groups.Add(group);
      await _context.SaveChangesAsync();
      return CreatedAtRoute("GetGroup", new { id = group.Id }, group);
    }

    return BadRequest(ModelState);
  }
  catch (Exception e)
  {
    return BadRequest($"Unable to create: {e.Message}");
  }
}

In my database, all columns, index and keys are created as expected and just like every other one to many relationship I've got in my API. But this specific case just seems to end up in misery...

The class I'm trying to add to the database:

Answer 1

Entity Framework lost tracking for some reason and Entity Framework needs to reestablish tracking for the entities that are already existing.

You can get the state of the entity's tracking with:

var entityTrackingState = _context.Entry(entity).State;

You can force Entity Framework to do tracking on the existing entities with:

_context.Entry(untrackedEntity).State = EntityState.Unchanged;

Where

_context 

is an Entity Framework DbContext.

Forcing tracking resolved my issue, but it really should be debugged where Entity Framework is losing tracking.

Answer 2

I had a similar problem when trying to save an entity (for ex., Cat) which had many-to-one relationships to existing entities (property Owner, pointing at Person). In addition to a) getting the relationship (Person) from the database before saving the entity (Cat), and b) adding another property (int PersonId) to the entity (Cat), I discovered what I think is the best solution: c) stick to "navigation" properties only (do not create extra int <<Name>>Id properties), and when referencing is needed, use cat.Owner = dbContext.Person.Attach(new Person { Id = 123 }).Entity;

Answer 3

Problem is that there's no hint for EF to know if Company (under Group relationship) is being explicitly inserted or if it is supposed to use the pre-existing one from database.

Since those instanced are disconnected from DbContext there is no indication whether they exist or not on the database by the time EF tries to generate its SQL command.

There is no easy way here, at least there was none by the time I've played with EF Core.

You should either:

1. Change your code to only use the ID instead of the navigation property so you'll avoid this whenever possible, or;
2. Change your code to fetch related data (eg: fetch Company and attach it to Group) before saving desired data (eg: before saving Group).

So, for instance:

var companyDB = await context.Companies.SingleOrDefaultAsync(c => c.Id == group.Company.Id);
group.Company = companyDB;
context.Groups.Add(group);
await context.SaveChangesAsync();

Yes, you're making two trips to database. That's why I'd suggest using the first approach, so you can avoid this fetch and just save Group entity directly into DB.

That does not, however, prohibits you from sending a navigation instace of Company to your view. Just create some entity-related classes which will correlate to your database so you can load/save data using this entity type, and create a Dto object which will be your endpoint input/output whenever needed.

Binding one into another can be done by using AutoMapper, manual linq or other approaches.

Answer 4

This is because you are passing some value in a column which is set as identity (auto-increment).

I think the Group entity which you are inserting has the companies with the value of Id which it tries to insert in company table as child record. and it throws an error.