Unsure how to insert item into database using Entity Framework with many to many relationship

Question

I am trying to insert a product into my database with an associated category. One product can belong to several categories and obviously one category can have several products. When I insert, I am sure I am missing something in my controller method but I'm not sure what it is. I have a bridge table called ProductCategory that just has a ProductID and a CategoryID in it. That table is not getting populated when I do the insert.

Here is my controller method that is doing the insert:

 [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult EditProduct([Bind(Include = "ID,itemNumber,product,description,active,PDFName,imageName,SelectedCategories")] ProductModel model)
    {
        if (ModelState.IsValid)
        {
            using (var context = new ProductContext())
            {
                context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                if (model.ID == 0)
                {
                    // Since it didn't have a ProductID, we assume this
                    // is a new Product
                    if (model.description == null || model.description.Trim() == "")
                    {
                        model.description = "Our Famous " + model.product;
                    }
                    if (model.imageName == null || model.imageName.Trim() == "")
                    {
                        model.imageName = model.itemNumber + ".jpg";
                    }
                    if (model.PDFName == null || model.PDFName.Trim() == "")
                    {
                        model.PDFName = model.itemNumber + ".pdf";
                    }
                    Session["dropdownID"] = model.ID;

                    // I think I probably need some additional code here...

                    context.Products.Add(model);
                }
                else
                {
                    // Since EF doesn't know about this product (it was instantiated by
                    // the ModelBinder and not EF itself, we need to tell EF that the
                    // object exists and that it is a modified copy of an existing row
                    context.Entry(model).State = EntityState.Modified;
                }
                context.SaveChanges();
                return RedirectToAction("ControlPanel");
            }
        }
        return View(model);
    }

And my Product model:

public class ProductModel
{
    public int ID { get; set; }

    [Required(ErrorMessage = "Required")]
    [Index("ItemNumber", 1, IsUnique = true)]
    [Display(Name = "Item #")]
    public int itemNumber { get; set; }

    [Required(ErrorMessage = "Required")]
    [Display(Name = "Product")]
    [MaxLength(50)]
    public String product { get; set; }

    [Display(Name = "Description")]
    [MaxLength(500)]
    public String description { get; set; }

    [DefaultValue(true)]
    [Display(Name = "Active?")]
    public bool active { get; set; }

    [Display(Name = "Image Name")]
    public String imageName { get; set; }

    [Display(Name = "PDF Name")]
    public String PDFName { get; set; }

    [Display(Name = "Category(s)")]
    public virtual ICollection<CategoryModel> ProductCategories { get; set; }

    public int[] SelectedCategories { get; set; }

    public IEnumerable<SelectListItem> CategorySelectList { get; set; }

    //public ICollection<CategoryModel> CategoryList { get; set; }

    public virtual BrochureModel Brochure { get; set; }

    public IEnumerable<SelectListItem> BrochureList { get; set; }

    [Display(Name = "Category(s)")]
    public String CategoryList { get; set; }

    public static IEnumerable<SelectListItem> getCategories(int id = 0)
    {
        using (var db = new ProductContext())
        {
            List<SelectListItem> list = new List<SelectListItem>();
            var categories = db.Categories.ToList();
            foreach (var cat in categories)
            {
                SelectListItem sli = new SelectListItem { Value = cat.ID.ToString(), Text = cat.categoryName };

                //if (id > 0 && cat.ID == id)
                //{
                //    sli.Selected = true;
                //}
                list.Add(sli);
            }
            return list;
        }

    }

    public ProductModel()
    {
        active = true;
    }

}

And my Category model:

public class CategoryModel
{
    public int ID { get; set; }

    [Required(ErrorMessage = "Required")]
    [Display(Name = "Category Name")]
    [MaxLength(50)]
    public String categoryName { get; set; }

    [MaxLength(50)]
    public String categoryDBName { get; set; }

    [DefaultValue(true)]
    [Display(Name = "Active?")]
    public bool isActive { get; set; }

    //public virtual ICollection<ProductCategory> ProductCategories { get; set; }

    public virtual ICollection<ProductModel> Products { get; set; }


}

Here is my Product context:

public class ProductContext : DbContext
{

    public ProductContext()
        : base("DefaultConnection")
    {
        Database.SetInitializer<ProductContext>(new CreateDatabaseIfNotExists<ProductContext>());
    }

    public DbSet<CategoryModel> Categories { get; set; }
    public DbSet<ProductModel> Products { get; set; }
    public DbSet<BrochureModel> Brochures { get; set; }


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        //modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        //modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

        modelBuilder.Entity<CategoryModel>().ToTable("Categories");
        modelBuilder.Entity<ProductModel>().ToTable("Products");
        modelBuilder.Entity<BrochureModel>().ToTable("Brochures");

        modelBuilder.Entity<ProductModel>()
        .HasMany(p => p.ProductCategories)
        .WithMany(p => p.Products)
        .Map(m =>
        {
            m.ToTable("ProductCategory");
            m.MapLeftKey("ProductID");
            m.MapRightKey("CategoryID");
        });

        //modelBuilder.Entity<CategoryModel>()
        //.HasMany(c => c.ProductCategories)
        //.WithRequired()
        //.HasForeignKey(c => c.CategoryID);

    }

    public System.Data.Entity.DbSet<newBestPlay.Models.RegisterViewModel> RegisterViewModels { get; set; }
}

Let me know if other code or more info is needed.

Is model.Id being assigned the correct value from the session variable before the add? — Casey
– Casey, Commented Jul 29, 2015 at 18:43
model.id is 0 going into the "add", which is true when a new product is being inserted — dmikester1
– dmikester1, Commented Jul 29, 2015 at 18:51
So is the ID a primary key on the table? If so are there already items with that ID stored in the table? — Casey
– Casey, Commented Jul 29, 2015 at 18:54
The ID is primary, but it is auto-incrementing so everytime I add a product it increases one. The product is getting added correctly into the Products table. It just not correctly linking to the category(s). — dmikester1
– dmikester1, Commented Jul 29, 2015 at 18:58
Did you use code first for setting up entity? If so can you post your DB context class? — Casey
– Casey, Commented Jul 29, 2015 at 19:02

Chris Pratt · Accepted Answer · 2015-07-30 16:43:03Z

1

You're never doing anything with your SelectedCategories array. You need to use this to pull CategoryModel instance from the database and then associate those with the product.

context.Categories.Where(c => model.SelectedCategories.Contains(c.ID)).ToList()
    .ForEach(c => model.ProductCategories.Add(c));

...

context.SaveChanges();

UPDATE

Can I ask how to list out the categories for each product in my view?

That's kind of a loaded question, as it's highly dependent on what type of experience you're trying to achieve. Generally speaking, with any collection, you'll need to iterate over the items in that collection and then render some bit of HTML for each item. You can do this in a number of different ways, which is why there's not really one "right" answer I can give you. However, just to give you an idea and not leave you with no code at all, here's a very basic way to just list out the name of every category:

@string.Join(", ", Model.ProductCategories.Select(c => c.categoryName))

edited Jul 30, 2015 at 16:43

answered Jul 29, 2015 at 20:27

Chris Pratt

240k37 gold badges414 silver badges469 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dmikester1 Over a year ago

Works perfect! Can I ask how to list out the categories for each product in my view?

Collectives™ on Stack Overflow

Unsure how to insert item into database using Entity Framework with many to many relationship

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related