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:
Accepted Answer
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
IDinstead of the navigation property so you'll avoid this whenever possible, or; - Change your code to fetch related data (eg: fetch
Companyand attach it toGroup) before saving desired data (eg: before savingGroup).
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.
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;
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow