SqlException: Cannot insert explicit value for identity column in table 'Recipe' when IDENTITY_INSERT is set to OFF

0
votes

Errors:

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

Program.cs

  1. I had add two category "Breakfast" and "Lunch".
  2. Could make a new Recipe there belongs to "BreakFast"
  3. If I try add a new recipe i get the error.
class Program
{
    static void Main(string[] args)
    {
        using (RecipesEntities context = new RecipesEntities())
        {
            //context.Categories.Add(new Category { Name = "Breakfast" });
            //context.Categories.Add(new Category { Name = "Lunch" });

            //new receipe and assign it to these two new categories

            // 1. Using Id properties 
            //Category category = context.Categories.FirstOrDefault(c => c.Name == "Breakfast");
            //context.Recipes.Add(new Recipe { Name = "Cereal", CategoryId = category.Id });


            // 2. Recipe.Category navigation property
            Category category = context.Categories.FirstOrDefault(c => c.Name == "Lunch");
            context.Recipes.Add(new Recipe { Name = "Pizza", Category = category });

            context.SaveChanges();
        }
    }
}

My database design

Diagram

Category Data

Category Design

Recipe Data Here should be a pizza.

Recipe Design

1
c#entity-frameworkado.net
Can you share your Recipe-class? - Compufreak
public int Id { get; set; } public string Name { get; set; } public Nullable<int> CategoryId { get; set; } public virtual Category Category { get; set; } - Henrik
You could try to add the [Key]-annotation to your Id and also check if this answer helps. - Compufreak
I get the same error :( - Henrik

1 Answers

0
votes

I know this is a bit old, but for anyone who sees this here is a link to the Microsoft Docs explaining how to fix it.

For your problem, you can try to fix it by surrounding your save with opening and closing of the Identity Insert.

context.Database.OpenConnection();
try
{
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Recipe ON");
    context.SaveChanges();
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Recipe OFF");
}
finally
{
    context.Database.CloseConnection();
}