Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF

Question

I'm attempting to set up a composite primary key with the second key being the year.

I've set both in the table creation and the constructor to auto generate the field but I still get this error:

Cannot insert explicit value for identity column in table Volunteer when IDENTITY_INSERT is set to OFF.

All three parts to the code and setup are below.

  [Table("Volunteer")]
    public class Volunteer
    {

        [Key][Column(Order = 0)]
        public int Id { get; set; }

        [DisplayName("First Name")]
        public string FirstName { get; set; }

        [DisplayName("Last Name")]
        public string LastName { get; set; }

        [DisplayName("Team Mentored")]
        public string TeamMentored { get; set; }


        [Key][Column(Order = 1)] [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public DateTime Year { get; set; }
        public string TableNumber { get; set; }
        public int OfficeId { get; set; }
        public string Image { get; set; }


    }

CREATE TABLE [dbo].[Volunteer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](255) NULL,
    [LastName] [nvarchar](255) NULL,
    [TeamMentored] [nvarchar](255) NULL,
    [Year] [date] DEFAULT(getdate()) NOT NULL,
    [TableNumber] [nvarchar](50) NULL,
    [OfficeId] [int] NOT NULL,
    [Image] [nvarchar](max) NULL
 )


public ActionResult Create([Bind(Include = "FirstName,LastName,TeamMentored,TableNumber,OfficeId,Image")] Volunteer volunteer)
        {
            try
            {

                if (ModelState.IsValid)
                {
                    db.Volunteers.Add(volunteer);
                    db.SaveChanges();

                    ModelState.Clear();
                } 

                // TODO: Add insert logic here

                return RedirectToAction("Index");
            }
            catch
            {
                ModelState.AddModelError("", "DB Update Failed!"); 
            }

            return View();
        }

Answer 1

Try using the attribute [DatabaseGenerated(DatabaseGeneratedOption.Identity)] on "Id" property. If that doesn't do it, is it possible to post the code of assignment and save?

Answer 2

Your attribute [DatabaseGenerated(DatabaseGeneratedOption.Identity)] tells Entity Framework (and SQL Server) that the property will be auto-generated by the database using an auto-incrementing value. Therefore, you can not provide it a value.

If it will work at all, you will have to move it to the Id property. It would probably work, but I can't say for certain as I haven't worked with a composite Key before.

If you are still getting the error, then it is because the database has been created with the Year column set as int identity(1,1). Either manually edit the database, or delete it and recreate it with a new migration.

Answer 3

I had to use two attributes on the ID property:

    [Key] // not [ExplicitKey]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
    public int Id { get; set; }

The first is from Dapper.Contrib.Extensions

the second from System.ComponentModel.DataAnnotations.Schema