Cannot Insert Explicit Value For Identity Column in Table 'Table' When Identity_Insert Is Set to Off

SqlException: Cannot insert explicit value for identity column in table [Table name] when IDENTITY_INSERT is set to OFF

I just changed Entry model from

public class Entry
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Description { get; set; }
    public decimal MoneyAmount { get; set; }
    public virtual Account Account { get; set; }
    public virtual Category Category { get; set; }
    public string CreatedTimestamp { get; set; }
}

To this:

 public class Entry
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Description { get; set; }
        public decimal MoneyAmount { get; set; }
        public int AccountId { get; set; }
        public int CategoryId { get; set; }
        public string CreatedTimestamp { get; set; }
    }

And everything seems to work fine now. C# does its magic and picks AccountId and CategoryId as foreign keys.

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 when identity insert is set to off

You need to apply annotation attribute to Id and it will do the job

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

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.

MVC - Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Your issue is that you're attempting to insert/update a database record and in the SQL being generated you're setting the field mapped to the t_bcim_project_clearance column to a specific value, however since you have this set to an identity you're not allowed to do this as its value is set automatically.

You need to either make sure the value in the object mapped to this column is not modified or marked as modified.

Instead of using entities.Entry(project).State = EntityState.Modified, set each property is actually in a modified state, to modified instead of the object itself. To set a property as modified you can do the following:

db.Entry(project).Property(x => x.PropertyName).IsModified = true;

When you are performing the .Add() make sure that the value of the property project_clearance_id is set to 0.

EDIT Additional info per comments:

Make sure to include the Html.HiddenFor(model => model.project_clearance_id) from your view if you removed it. This is needed if you're performing an update.
Remove the [Bind(Exclude = "project_clearance_id")] attribute from your arguments because you need this value if you're performing an update.

SQL Server error: Cannot insert explicit value for identity column even when I SET IDENTITY_INSERT ON

Thank you so much @Sami, you help me to realize the right path.
It turns out, you can just use, IDENTITY_INSERT to one table at time (for obvious reasons is not a thing I do often).
When I did for several tables at time, I saw the error, but as the name of the tables were similar, I thought it was throwing an error because I ran the Identity_insert before on the same table, but it was because it was taken by the other table.
I didn't realized until I review the Error messages one by one. :P :D

https://dba.stackexchange.com/questions/12650/why-is-identity-insert-on-only-allowed-on-one-table-at-a-time

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.