Entity Framework Error: Cannot Insert Explicit Value for Identity Column in Table

Entity Framework error: Cannot insert explicit value for identity column in table

I have run into this before. This error means you are trying to assign a value explicitly to a column where the database automatically assigns it.

Suggestion:
Update your edmx file to reflect any changes you may have made in the database.
If the database automatically assigns the value, you should see the "IsDbGenerated=true" attribute in your designer file under that property. If it's not there, you can add it manually.

EF Core SqlException: Cannot insert explicit value for identity column in table 'MenuCategories' when IDENTITY_INSERT is set to OFF

I have to assume that your DbContext class is like this:

public class Context: DbContext
{
public Context(DbContextOptions<MenuContext> options)
: base(options)
{ }

public DbSet<MenuCategory> MenuCategories{ get; set; }
public DbSet<MenuItem> MenuItems { get; set; }
}

So your controller can be coded this way:

public async Task<IActionResult> Create(MenuItemViewModel model)
{
if (ModelState.IsValid)
{
var menuItem = new MenuItem
{
Name = model.Name,
...
};

var menuCategory = _Context.MenuCategories.Find(model.MenuCategory.MenuCategoryId);
menuItem.MenuCategory = menuCategory;
_Context.MenuItems.Add(menuItem);
await _Context.SaveChangesAsync();
return RedirectToAction(nameof(Index), new { id = menuItem.MenuCategory.MenuCategoryId });
}
return View(menuItem);
}

From what I can tell, you are assuming that if you tell your View that your MenuCategory has a selected id, your view will populate the MenuCategory. It does not work that way. The view does not have a way of knowing what your MenuCategory is. You need to populate the values with your DbContext, which happens in your controller.

Also, it would be best if you separate your entities from your view models. It is so much easier if your classes have their own simple responsibility: your entities will represent a logical record in your database, and your view model will represent the data that comes from and goes to your controllers.

Cannot insert explicit value for identity column in table 'TABLE' when IDENTITY_INSERT is set to OFF Exception Updating my table

The error specifically is the result of a configuration mismatch somewhere in your entity definitions where a table is set to use an Identity PK but EF is not configured for an identity column and the DbContext is attempting to insert a row into that table. This is not necessarily the table you think the DbContext is attempting to update. Chances are somewhere prior to SaveChanges being called there has been an entity incorrectly created or associated and treated as a new entity.

Try this, provided your code is roughly identical to your example:

using (var tempContext = new YourAppDbContext())
{
foreach (int i in deletes)
{
var p = tempContext.Item.FirstOrDefault(g => g.participant_id == i);

if (p != null)
p.group_id = 0;
}
tempContext.SaveChanges();
}

Here we isolate this operation to a clean, temporary DbContext instance to ensure there are no polluting tracked changes. This is not a "fix" for the issue, just to identify whether the context was polluted. You can inspect context pending changes to see what it might be tracking. (ChangeTracker) Your issue may also stem from setting the entire entity to Modified state and a misconfigured related entity definition. (unlikely if the entity and associated were read from this DbContext instance) When updating an entity, don't set the EntityState to Modified. This sets EF up to effectively update all columns on that table. In the above example if we just update 1 column, EF will generate an Update statement for just that single column, and will only generate an update statement if that value actually changes.

If the above code works without an issue, then you have rogue tracked changes polluting your DbContext. This either means you have something else going on before this code is called that you need to fix, or your DbContext lifetime scope is far longer than it should be. (or both)

The problem with your example is that we cannot see the scope that your context is covering. Whenever you make any kind of change to a DbContext, those changes are tracked by default by that DbContext. If you have code that is working with detached entities anywhere that suddenly get re-associated with a DbContext via association with an Added entity and treated as new entities themselves, then calling SaveChanges() when you expect to update a single row all the sudden results in strange unrelated exceptions because the DbContext wants to persist queued up changes.

A typical culprit to look out for with issues like this would be first to check over all of your entity definitions for tables using Identity PKs and ensure those entities are configured with DatabaseGeneratedOption.Identity. This alone would prevent the exception, but you'd probably find that EF would be inserting new, duplicate rows somewhere with new PKs. The culprit behind that is usually detached entities being re-associated to an entity.

Given a Order entity associated to a Product. We want to create an order so since we already loaded the Products collection to select and associated to the new Order, we set that reference client side and send the Order to the server. We assume that since we loaded the Products and it's children from a DbContext on a GET, that when we call POST and send that parent back to the server, all should be good.

public void CreateOrder(Order order)
{
context.Orders.Add(order);
context.SaveChanges();
}

order had a Product reference with ProductId = 22 since we selected product #22 for the order. However, the DbContext instance when we call CreateOrder doesn't know Product ID 22 represents a known entity, it isn't tracking Product ID 22, so it gets treated as a new entity. If the Product entity ID isn't configured as an Identity column but is in the DB, you get the error you saw. If it is configured as an Identity column then you'd find the Order saves successfully, but is pointing at a new, duplicate Product with an ID of 56 or whatever the next available ProductId is.

Passing detached entities is a big cause of headaches like this. The safe way to do something like the CreateOrder would be:

public void CreateOrder(Order order)
{
// TODO: Obviously add null checks, validations, etc.
var product = context.Products.Single(x => x.ProductId == order.Product.ProductId);
order.Product = product; // replace with the product tracked by this DbContext.
context.Orders.Add(order);
context.SaveChanges();
}

All associations on a detached entity need to be checked.

Devs don't like the hit to the database so they can be tempted to merely attach the Product:

public void CreateOrder(Order order)
{
// TODO: Obviously add null checks, validations, etc.
context.Products.Attach(order.Product);
context.Orders.Add(order);
context.SaveChanges();
}

.. and in most cases that will appear to work. Until it doesn't. Attach will throw an exception the moment you attempt to attach an entity with an ID that the DbContext is already tracking. If any code prior had loaded or attached an instance of that Product, then the call will fail. This can result in random-like exceptions appearing at runtime. The safe way to attach the entity would be:

public void CreateOrder(Order order)
{
// TODO: Obviously add null checks, validations, etc.
var existingProduct = context.Product.Local.SingleOrDefault(x => x.ProductId == order.Product.ProductId);
if (existingProduct == null)
context.Products.Attach(order.Product);
else
order.Product = existingProduct;

context.Orders.Add(order);
context.SaveChanges();
}

This checks the local tracking cache to see if the DbContext is tracking that product. (Does not hit DB) If it isn't then we can safely attach it. If it is, we replace the product reference on the order with the tracked one.

This would have to be done for every reference. Any that are missed would result in potential errors or duplicate rows.

Cannot insert explicit value for identity column in table 'x' when IDENTITY_INSERT is set to OFF - inserting record with nested custom object

This issue typically happens when deserializing entities that have related entities in the object graph then attempting to add them. UserPermission is likely an existing record that in the DB is set up with an identity PK, but EF doesn't appear to recognize that in the entity definition. (I.e. set to DatabaseGenerated(DatabaseGeneratedOption.Identity). If it had been you would most likely be seeing a different problem where a completely new duplicate UserPermission was being created.

If someUser, and it's associated someUser.UserPermission are deserialized entities then you need to do a bit of work to ensure EF is aware that UserPermission is an existing row:

void AddUser(User someUser)
{
var existingPermission = _context.UserPermissions.Local
.SingleOrDefault(x => x.UserPermissionId == someUser.UserPermission.UserPermissionId);
if (existingPermission != null)
someUser.UserPermission = existingPermission;
else
_context.Attach(someUser.UserPermission);

_context.Users.Add(someUser);
_context.SaveChanges();
}

In a nutshell, when working with detached entities that a DbContext may not be tracking, we need to check the Local state for any existing tracked instance for that ID. If we find one, we substitute the detached reference for the tracked one. If we don't find one, we attach the detached one before Adding our user.

This still isn't entirely safe because it assumes that the referenced UserPermission will exist in the database. If for any reason a non-existent UserPermission is sent in (row deleted, or fake data) you will get an exception on Save.

Passing detached entity references around can seem like a simple option at first, but you need to do this for every reference within a detached entity. If you simply call Attach without first checking, it will likely work until you come across a scenario where at runtime it doesn't work because the context happens to already be tracking an instance.

EF Core Cannot insert explicit value for identity column in table

The reason of this behaviour is that Automapper creates a new instance and Entity Framework thinks that POCO's are all new entities which should be inserted.

So the solution is to Attach existing entities to `DbContext. It is described in great tutorial here.

So the solution looks like this:

public async Task Add(Order order)
{
_context.Attach(order.Customer);
foreach (var orderItem in order.OrderItems)
{
_context.Attach(orderItem.Product);
}
await _context.Orders.AddAsync(order);
}

The whole code looks like this.

Service Layer:

public async Task<OrderDto> Add(OrderDto orderDto)
{
var order = _mapper.Map<Order>(orderDto);
await _orderRepository.Add(order);
orderDto.Id = order.Id;
return orderDto;
}

Repository Layer:

public async Task Add(Order order)
{
_context.Attach(order.Customer);
foreach (var orderItem in order.OrderItems)
{
_context.Attach(orderItem.Product);
}
await _context.Orders.AddAsync(order);
}

EF core cannot insert explicit value for identity column when using BYTE as identity column type in Sql Server

The issue was using byte as the data type for the identity column. I am not sure why as I know that I can manually create an identity column in sql server with type byte. Something in EF Core is choking.

THE SOLUTION:

I changed the identity column data type to int and the insert works properly with auto incrementing id values.

Maybe I missed something else but its working and these table will not have many records so I will just use int and continue on, haha.



Related Topics



Leave a reply



Submit