Many-To-Many Relationship Basic Example (MVC3)

Question

I have a MVC3 C# project that I have a model of FoodItem and FoodItemCategory. The two models are shown as follows:

public class FoodItem
{
    public int ID { get; set; }
    [Required]
    public string Name { get; set; }
    public string Description { get; set; }
    public virtual ICollection Categories { get; set; }
    public DateTime CreateDate { get; set; }
}

public class FoodItemCategory {
    public int ID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public virtual ICollection FoodItems { get; set; }
    public DateTime CreateDate { get; set; }
}

I have a _CreateOrEdit.cshtml view that was initially generated from the Scaffolder and I modified it to include all of the Categories and check the box that the food item belongs to. A food item could have many or all of the categories. The view looks like the following:

@model StackOverFlowIssue.Models.FoodItem

    @Html.LabelFor(model => model.Name)


    @Html.EditorFor(model => model.Name)
    @Html.ValidationMessageFor(model => model.Name)


    @Html.LabelFor(model => model.Description)


    @Html.EditorFor(model => model.Description)
    @Html.ValidationMessageFor(model => model.Description)


    @Html.LabelFor(model => model.Categories)


    @foreach (var FoodItemCategory in (IEnumerable)ViewBag.Categories){
        
        @FoodItemCategory.Name 
        

    } 

@Html.Hidden("CreateDate", @DateTime.Now)

As you can see, I have a nested loop that creates a checkbox for each category, and while it is creating each category, I loop through and check for that particular category in the Categories property of my model. If it exists, I set the checked property of the checkbox. If you check a box and click save, on the HttpPost action on the controller, I am performing the following:

    [HttpPost]
    public ActionResult Edit(FoodItem foodItem)
    {
        if (ModelState.IsValid)
        {
            var cList = Request["CategoryId"].Split(',');
            List categories = new List();

            foreach (var c in cList) {
                var ci = Convert.ToInt32(c);
                FoodItemCategory category = context.FoodItemCategories.Single(x => x.ID == ci);
                categories.Add(category);
            }

            context.Entry(foodItem).State = EntityState.Modified;
            restaurant.Categories = categories;
            context.SaveChanges();
            return RedirectToAction("Index");
        }
        return View(foodItem);
    }

I am able to save the categories one time. If I go back into the view, and just click save, I receive the following error:

A duplicate value cannot be inserted into a unique index. [ Table name = >FoodItemCategoryFoodItems,Constraint name = PK_FoodItemCategoryFoodItems_00000000000000A8 ] Description: An unhandled exception occurred during the execution of the current web request. Please >review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlServerCe.SqlCeException: A duplicate value cannot be inserted into >a unique index. [ Table name = FoodItemCategoryFoodItems,Constraint name = >PK_FoodItemCategoryFoodItems_00000000000000A8 ]

Source Error:

Line 97: context.Entry(foodItem).State = EntityState.Modified; Line 98: foodItem.Categories = categories; Line 99: context.SaveChanges(); Line 100: return RedirectToAction("Index"); Line 101: }

Not sure if it matters but I am using SQLServer Compact Edition 4. Am I going about this the right way? What is the normal coding practice for something like this? I know this same situation occurs daily since this same relationship model is used in many situations like blogs, etc.

Ladislav Mrnka · Accepted Answer

Try something like this (untested):

[HttpPost]
public ActionResult Edit(FoodItem foodItem)
{
    if (ModelState.IsValid)
    {
        int id = foodItem.Id;
        // Load food item with related categories first
        var item = context.FoodItems
                          .Include(f => f.Categories)
                          .Single(f => f.Id == id);

        // Process changed scalar values
        context.Entry(item).CurrentValues.SetValues(foodItem);

        // Brute force processing of relations
        // This can be optimized - instead of deleting all and adding all again
        // you can manually compare which relations already exists, add new and
        // remove non existing but let's make that as a homework
        item.Categories.Clear();

        var cList = Request["CategoryId"].Split(',');

        foreach (var c in cList) 
        {
            var ci = Convert.ToInt32(c);
            // Use find - if category was already loaded in the first query, it will
            // be reused without additional query to DB
            var category = context.Categories.Find(ci);
            // Now add category to attached food item to create new relation
            item.Categories.Add(category);
        }

        context.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(foodItem);
}

This can look pretty inefficient but because you are dealing with many-to-many relation where relations can be added or removed in the view it is the only way to do that. The reasons are:

You must say EF which relations are added and which are deleted
If you simply add all related categories you are inserting relations again
You can't say EF which relations are deleted because you don't transfer information about unchecked categories

More about detached object graphs and processing relations is described here. It is about ObjectContext API but DbContext API is just wrapper around that so the same limitations still exist.

Many-To-Many Relationship Basic Example (MVC3)

Answers (2)

Related Questions