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

Question

I´m trying to update a list of objects, but when I run context.SaveChanges(), I get an error

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

I know when the Identity key is generated you cannot register a new entry with this KEY but I'm trying to update my registry not insert a new one and this exception appears. I don't know why this happens now.

My code:

foreach (int i in deletes)
{
    var p = context.Item.FirstOrDefault(g => g.participant_id == i);

    if (p != null)
    {
        p.group_id = 0;
    
        context.Entry(p).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
        context.SaveChanges();
    }
}

group_id is not my identity column, it is just another column I added:

ALTER TABLE Items
ADD group_id INTEGER DEFAULT(0);

Answer 1

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.

Answer 2

Try

var p = context.DbSet<Participant>().Where(g => g.participant_id == i).FirstOrDefault();  

And did you check your Participant class in EF? It still can be a foreign key there.