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

Question

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

asp.net-core entity-framework-core sql-server

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:

MysticEarth

Popular Answer

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;

Mr. TA

View more on Stack Overflow

Anderson Matos · Accepted Answer · 10/5/2018 5:02:09 PM

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:

Change your code to only use the ID instead of the navigation property so you'll avoid this whenever possible, or;
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.

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related