c# - "Cannot insert explicit value for identity column in table 'Movies' when IDENTITY_INSERT is set to OFF."

Question

I'm using code first with entity framework. I have been getting the error below and can't figure out how to fix it:

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

I've read that setting Sql("SET IDENTITY_INSERT Movies ON") and OFF around my migration query should fix this however I did not run any queries on the Movies table.

Movies table:

{
    public class Movies
    {
        public byte Id { get; set; }

        [Display (Name = "Movie Name")]
        public string MovieName { get; set; }

        public Genre Genre { get; set; }

        [Required]
        public byte GenreId { get; set; }

        [Display (Name = "Release Date")]
        public DateTime ReleaseDate { get; set; }

        public DateTime DateAdded { get; set; }

        [Display (Name = "Numbers in Stock")]
        public int NumberInStock { get; set; }
    }
}

My Movies controller:

public ActionResult Save(Movies movies) {
        if (movies.Id == 0)
        {
            _context.Movies.Add(movies);
        }
        else {
            var moviesInDb = _context.Movies.Single(c => c.Id == movies.Id);
            moviesInDb.MovieName = movies.MovieName;
            moviesInDb.ReleaseDate = movies.ReleaseDate;
            moviesInDb.GenreId = movies.GenreId;
            moviesInDb.NumberInStock = movies.NumberInStock;
        }

        _context.SaveChanges();

        return RedirectToAction("Index, Movies");
    }

I am getting the error on _context.SaveChanges();

I do have queries for my Genre table which is as below

public partial class PopulateGenreTable : DbMigration
{
    public override void Up()
    {            
        Sql("INSERT INTO Genres (Id, Name) VALUES (1, 'Action')");
        Sql("INSERT INTO Genres (Id, Name) VALUES (2, 'Thriller')");
        Sql("INSERT INTO Genres (Id, Name) VALUES (3, 'Family')");
        Sql("INSERT INTO Genres (Id, Name) VALUES (4, 'Romance')");
        Sql("INSERT INTO Genres (Id, Name) VALUES (5, 'Comedy')");            
    }

    public override void Down()
    {
    }
}

That's the only place I've seeded the dabase

How do I fix this? Please explain clearly as I am an absolute beginner. Thanks

Answer 1

You have to first turn it ON, then do your insert and then turn it OFF

Sql("SET IDENTITY_INSERT Genres ON");

Sql("INSERT INTO Genres (Id, Name) VALUES (1, 'Action')");
Sql("INSERT INTO Genres (Id, Name) VALUES (2, 'Thriller')");
Sql("INSERT INTO Genres (Id, Name) VALUES (3, 'Family')");
Sql("INSERT INTO Genres (Id, Name) VALUES (4, 'Romance')");
Sql("INSERT INTO Genres (Id, Name) VALUES (5, 'Comedy')");

Sql("SET IDENTITY_INSERT Genres OFF");

But, the point of having an identity column is that we don't choose what values the identity holds (read up on surrogate keys). In that case, you just remove the ID column from your insert

Sql("INSERT INTO Genres (Name) VALUES ('Action')");
Sql("INSERT INTO Genres (Name) VALUES ('Thriller')");
Sql("INSERT INTO Genres (Name) VALUES ('Family')");
Sql("INSERT INTO Genres (Name) VALUES ('Romance')");
Sql("INSERT INTO Genres (Name) VALUES ('Comedy')");