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

Question

I am trying to save some data into my MenuItems table but I'm prevented by the error

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

I am just trying to set the foreign key using the category id I have, I am not quite sure why I am getting the above error.

This is my MenuItem model:

public class MenuItemViewModel
{
    [Key]
    [Column("MenuItem_Id")]
    public int MenuItemId { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Description { get; set; }

    [Required]
    public decimal Price { get; set; }

    [Required]
    [Column("MenuCategory_Id")]
    public MenuCategoryViewModel MenuCategory { get; set; }
}

This is my MenuCategory model:

public class MenuCategoryViewModel
{
    [Key]
    [Column("MenuCategory_Id")]
    public int MenuCategoryId { get; set; }

    [Required]
    public string Name { get; set; }
}

I'm trying to assign it the id of an existing category, I do not want it to create a new category.

Answer 1

Based on your comments of you wanting to use an existing Id to not add a new category, then what you are doing for your save is a little wrong _Context.Add(menuItem); will cause a database INSERT to fire - this is because the .Add() marks all of the entities as new entities to be saved to the context, yet you arent wanting that - what you should do is something along the lines of Add the menuItem - Query the Database to get the MenuCategory that you want to have attached to it - and then set that on the menuItem and then save.

Answer 2

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.

Answer 3

You can also get this error if you've 'implicitly' added an entity to the database, saved it and then try to manually add it.

Clearly the following is HORRIBLE code and would make no sense to write this. However it's possible to do this accidentally with a more complicated situation.

var category = new MenuCategoryViewModel() { ... };

// assign category to menu item    
var model = new MenuItemViewModel() 
            {
                MenuCategory = category 
            };  

db.Add(model);
db.SaveChanges();

// 'category' object has an ID because we saved it
// even though it was never explicitly added to the context

// add the category to the context 
db.Add(category);
db.SaveChanges();  // fails with IDENTITY error

If you are adding an entity without explicitly adding it to the context you mustn't add it later after it already has been assigned a key.