2

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

ECL-94
  • 97
  • 3
  • 9
  • Managed to fix the problem by adding [DatabaseGenerated(DatabaseGeneratedOption.Identity)] to my Id property in the Movies class – ECL-94 Nov 19 '17 at 18:24

2 Answers2

2

You could easily avoid this error by not specifying the Id, when you insert a record to the Genres table. As it seems Id is an IDENTITY COLUMN. Hence you don't have to specify a value when you insert there a record. The database would generate always the correct Id value based on the IDENTITY you have defined. Usually we have Columns of type INT and IDENTITY(1,1). That means that the first row that would be inserted would have as an Id the value of 1. The second row the value of 2 and so on and so forth.

For further info please have a look here.

Regarding the error message you get about to IDENTITY_INSERT..., the command SET IDENTITY_INSERT can be used when we want to insert one (or more) explicit value(s) to a column with an IDENTITY. A detailed explanation on how to use this command you can find here.

Christos
  • 53,228
  • 8
  • 76
  • 108
  • 1
    A use case I have for this is when I want to pre-seed a table that needs an automatic identity column. i.e. I have some rows with Ids that I would prefer not to change when migrating to a new server. but the same table may get new inserts and I would like those to get assigned auto ids – Zach Smith Jun 21 '18 at 06:28
2

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')");
Raj More
  • 47,048
  • 33
  • 131
  • 198