Invalid column name for SQL Server update-database

Question

I'm trying to populate a table of genres but continually get an error:

Invalid column name

I have a simple class model for movie genres.

public class Genre
{
    public int Id { get; set; }
    public string Name { get; set; }
}

A movie class is tied to the genre like this:

public class Movie
{
    public int Id { get; set; }

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

    [Required]
    public Genre Genre { get; set; }

    [Required]
    public DateTime ReleaseDate { get; set; }

    [Required]
    public DateTime DateAdded { get; set; }

    [Required]
    public int  NumInStock { get; set; }
}

In my Nuget console, I run add-migration and it generated an empty Genre table for me with two columns, Id & name.

Then, I try and populate the genre table with this SQL query:

public override void Up()
{
    Sql("INSERT INTO Genres (Id, Name) VALUES (1, Fantasy)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (2, Drama)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (3, Action-Adventure)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (4, Foreign)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (5, Horror)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (6, Romance)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (7, Crime)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (8, Thriller)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (9, Animated)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (10, Western)");
}

Not sure what I'm doing wrong. if I put 'fantasy' in quotes (because it is maybe expecting a string?) then I receive this error:

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

Check your Genres table's definition, Id is a system generated column Identity column, without specifying SET INDENTITY_INSERT to ON, you cannot insert your own values into this column even though they are identical. — LONG

sgeddes sgeddes · Accepted Answer · 2018-06-12T17:10:50

You definitely need the single quotes to insert string values. From your error message, the Id column is an auto increment (identity) column. So you can't explicitly define those values without setting identity_insert.

Easiest fix is to remove the Id from your insert and allow the database to maintain those values. For example:

insert into genres (name) values ('Fantasy')

Invalid column name for SQL Server update-database

2 Answers