System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'IngredientId', table 'RecipeApplicationDb.dbo.IngredientModels';

Question

I have been looking for a way to automatically generate in ID for an ASP.NET MVC application. This is because I get this error when trying to update the database:

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'IngredientId', table 'RecipeApplicationDb.dbo.IngredientModels'; 

This is the Model:

public class IngredientModel
 {
        [Key]
        public int IngredientId { get; set; }

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

This is the controller:

public class IngredientController : Controller
{
    private RecipeApplicationDb db = new RecipeApplicationDb();

    public ActionResult Index()
    {
        //var ingredients = db.Ingredients.Include(i => i.DefaultUOM);
        return View();
    }

    public ActionResult Create()
    {
        return View();
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create([Bind(Include = "IngredientId,Name,SeasonStartdate,SeasonEnddate")] IngredientModel ingredientModel)
    {
        if (ModelState.IsValid)
        {
            db.Ingredients.Add(ingredientModel);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        ViewBag.IngredientId = new SelectList(db.UnitOfMeasures, "UnitOfMeasureId", "Name", ingredientModel.IngredientId);
        return View(ingredientModel);
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }
}

I think the best way to solve this is to use a Linq expression to generate IngredientId. For this I have found (among others) this post: How do I use Linq to obtain a unique list of properties from a list of objects? Could someone tell me if this is the right solution, and if so, where I should put this code.

Thanks in advance.

=============EDIT=====================

I have also created a configuration file with this content:

        #region Ingredienten

        var italiaanseHam = new IngredientModel { Name = "Italiaanse Ham", SeasonStartdate = DateTime.Now, SeasonEnddate = DateTime.Now };
        var zachteGeitenkaas = new IngredientModel { Name = "Zachte Geitenkaas", SeasonStartdate = DateTime.Now, SeasonEnddate = DateTime.Now };
        var gedroogdeVeenbessen = new IngredientModel { Name = "Gedroogde Veenbessen", SeasonStartdate = DateTime.Now, SeasonEnddate = DateTime.Now };
        var gemsla = new IngredientModel { Name = "Gemsla", SeasonStartdate = DateTime.Now, SeasonEnddate = DateTime.Now };
        var stilton = new IngredientModel { Name = "Stilton", SeasonStartdate = DateTime.Now, SeasonEnddate = DateTime.Now };

        if (!context.Ingredients.Any())
        {
            context.Ingredients.AddOrUpdate(
                i => i.IngredientId,
                italiaanseHam,
                zachteGeitenkaas,
                gedroogdeVeenbessen,
                gemsla,
                stilton
                );
        };

        #region Recipes
        var LittleGemsal = new RecipeModel
        {
            Name = "Little Gemsla",
            Description = "Little Gemsla met geitenkaas, veenbessen, gedroogde ham en stilton",
            ImageUrl = "http://google.com",
            RecipeLines = new List<RecipeLine> {
                new RecipeLine { Quantity = 1, UnitOfMeasure = plak, Ingredient = italiaanseHam },
                new RecipeLine { Quantity = 100, UnitOfMeasure = gram, Ingredient = zachteGeitenkaas },
                new RecipeLine { Quantity = 2, UnitOfMeasure = eetlepel, Ingredient = gedroogdeVeenbessen },
                new RecipeLine { Quantity = 4, UnitOfMeasure = stuks, Ingredient = gemsla },
                new RecipeLine { Quantity = 1, UnitOfMeasure = stuks, Ingredient = stilton },
            }
        };

        if (!context.Recipes.Any())
        {
            context.Recipes.AddOrUpdate(
                i => i.RecipeId,
                LittleGemsal
                );
        };
        #endregion

And to update the database I use Package Manager Console with the command:

Update-Database -Verbose -Force

Also I use LocalDb for this project, do Database manager does not find the database.

Answer 1

In the IngredientModel class where you definied your model add ? after int as shown below:

public int? IngredientsId {get; set;}     

This should bypass the value allowing you to enter null (so you can check if it was entering a value when you ran your code).

If it was entering null then remove the data annotation [Key]. By default your first line in the model class will be your primary key.

If that does not work use this annotation instead [HiddenInput(DisplayValue = true)].

It it does not solve it read a few data annotations.

Answer 2

You can use data adnotation on IngredientId field :

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Hope it helps.

Answer 3

I have encountered this problem multiple times while working with Microsoft SQL Server and I have followed the same way to fix it. To solve this problem, make sure Identity Specification is set to Yes. Here's how it looks like:

In this way the column number auto increments as a primary key normally would.

HOW?: right-click the table that contains the column, choose Design, select the primary key and in Column properties window find Identity Specification and set it to Yes.