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

Question

When submitting my create form for my model, I get the error:

{"Cannot insert explicit value for identity column in table 't_bcim_project_clearance' when IDENTITY_INSERT is set to OFF."}

I can see this question has been asked a lot but I've made many changes to my code with no luck at all. I'm sorry in advance that it's so much code, I usually get people complaining in the comments that I don't post enough code so I tried to include everything that could possibly be relevant this time.

The method in my controller that throws this error is:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult MaintenanceForm([Bind(Exclude = "project_clearance_id")]t_bcim_project_clearance project)
{
    if (ModelState.IsValid)
    {
        if (entities.t_bcim_project_clearance.Any(x => x.project_clearance_id == project.project_clearance_id))
        {
            entities.Entry(project).State = EntityState.Modified;
        }
        else
        {
            entities.t_bcim_project_clearance.Add(project);
        }
        entities.SaveChanges();
        return RedirectToAction("Maintenance", new { SubmissionNumber = project.Submission_Number, Mode = "Edit" });
    }
    var model = new ProjectViewModel()
    {
        Project = entities.t_bcim_project_clearance.Find(project.project_clearance_id),
        States = states,
        Branches = branches,
        Divisions = divisions,
        ProjectTypes = project_types,
        Statuses = statuses,
        ReadOnly = false,
        Mode = "Edit",
        Cleared = false
    };

    return RedirectToAction("Maintenance", new { SubmissionNumber = project.Submission_Number, Mode = "Edit" });
}

at the line entities.SaveChanges();. entities is my Context class for connecting to the SQL database.

Here's the corresponding GET method:

[HttpGet]
public ActionResult MaintenanceForm(string SubmissionNumber, string Mode, int? id)
{
    if (!Mode.Equals("Edit", StringComparison.InvariantCultureIgnoreCase) && !Mode.Equals("View", StringComparison.InvariantCultureIgnoreCase))
    {
        throw new HttpException(400, "Mode must be either \"Edit\" or \"View\"");
    }

    var model = new ProjectViewModel()
    {
        Project = id.HasValue ? entities.t_bcim_project_clearance.Find(id) : (String.IsNullOrEmpty(SubmissionNumber) ? null : new t_bcim_project_clearance() { Submission_Number = Convert.ToDouble(SubmissionNumber) }),
        Statuses = statuses,
        Divisions = divisions,
        Branches = branches,
        ProjectTypes = project_types,
        States = states,
        Projects = entities.t_bcim_project_clearance.Where(s => s.Submission_Number.ToString().Equals(SubmissionNumber)),
        SubmissionNumber = SubmissionNumber,
        ReadOnly = (Mode == "View"),
        Mode = Mode,
        Cleared = false
    };

    return PartialView("_MaintenanceForm", model);
}

My model is a DBmodel. This is the offending property in that model:

namespace ProjectClearanceApp.Models
{
    using System;
    using System.Collections.Generic;

    public partial class t_bcim_project_clearance
    {
        // A lot of other properties
        public int project_clearance_id { get; set; }
    }
}

and I created a partial class to add DataAnnotations, which is here:

namespace ProjectClearanceApp.Models
{
    [MetadataType(typeof(t_bcim_project_clearanceMetaData))]
    public partial class t_bcim_project_clearance
    {
    }

    public class t_bcim_project_clearanceMetaData
    {
        // a lot of other properties

        [Key]
        [Required]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int project_clearance_id { get; set; }
    }
}

My project_clearance_id is set as the primary key for my table, with the Identity Increment = 1.

WHAT I'VE TRIED SO FAR:

• Originally my controller was generating new projects with a precalculated ID, so obviously I removed that
• I added all of the DataAnnotations to the ID property in that partial class
• I used to have Html.HiddenFor(model => model.project_clearance_id) at the top of my form but I removed that
• I added the [Bind(Exclude = "project_clearance_id")] to the arguments for the POST method

Nothing seems to be working. If anyone sees anything I could change that would be appreciated.

EDIT: I went into the .edmx diagram for my model, right clicked on the project_clearance_id, opened properties, and changed StoreGeneratedPattern to Identity. Now the code works fine when I'm trying to create a new entry, but when I try to edit an existing entry, it creates a new entry with the modified fields and the original entry still exists as it was before.

Answer 1

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.