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:

You need to make sure that the EF mapping for the entity Company and the DB Schema for table Companies are aligned. If you want to use IDENTITY for insert then make sure the tables schema is set with IDENTITY_INSERT and that the mapping has been configured for Identity Insert (this last part is likely your problem). If you do not want to use it then make sure you are passing an explicit value in the property. — Igor
– Igor, Commented Oct 5, 2018 at 11:32
The error is pretty straight-forward. You're passing an id for your group when attempting to create it in the first place. The id is an auto-incremented primary key, so the id comes from the database, not you. Don't set an explicit id, and you're fine. — Chris Pratt
– Chris Pratt, Commented Oct 5, 2018 at 13:30
@ChrisPratt it sounds pretty simple, but the thing is that i'm not setting an explicit id and that EF handles everything... — MysticEarth
– MysticEarth, Commented Oct 8, 2018 at 6:34

Anderson Matos · Accepted Answer · 2018-10-05 17:02:09Z

2

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.

answered Oct 5, 2018 at 17:02

Anderson Matos

3,1271 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MysticEarth Over a year ago

Number 2 of your solution was the actual solution for this particular problem. When posting a new Group with a list of Companies, Company.GroupID is 0, which is the explicit value mentioned in the error. Creating a new group and adding a list of companies to it solved the problem.

Edminsson Over a year ago

@AndersonMatos Would you please add an example of the first approach in this particular case?

Anderson Matos Over a year ago

You mean the ID-only approach? You simply add the ID field with corresponding mappings but without any navigational property.

Stephen Himes · Accepted Answer · 2021-05-04 14:32:07Z

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.

answered May 4, 2021 at 14:32

Stephen Himes

7359 silver badges14 bronze badges

Comments

Tech Yogesh · Accepted Answer · 2018-10-05 11:20:03Z

0

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.

answered Oct 5, 2018 at 11:20

Tech Yogesh

4574 silver badges21 bronze badges

Comments

Mr. TA · Accepted Answer · 2020-04-09 15:14:37Z

0

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;

answered Apr 9, 2020 at 15:14

Mr. TA

5,3771 gold badge31 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related