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<FoodItemCategory> 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<FoodItem> 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
<div class="editor-label">
    @Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
    @Html.EditorFor(model => model.Name)
    @Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
    @Html.LabelFor(model => model.Description)
</div>
<div class="editor-field">
    @Html.EditorFor(model => model.Description)
    @Html.ValidationMessageFor(model => model.Description)
</div>
<div class="editor-label">
    @Html.LabelFor(model => model.Categories)
</div>
<div class="editor-field">
    @foreach (var FoodItemCategory in (IEnumerable<StackOverFlowIssue.Models.FoodItemCategory>)ViewBag.Categories){
        <input type="checkbox" name="FoodItemCategoryId" value="@FoodItemCategory.ID" 
        @foreach(var c in Model.Categories){
            if(c.ID == FoodItemCategory.ID){ 
                @String.Format("checked=\"checked\"")
            } 
        } 
        />
        @FoodItemCategory.Name 
        <br />
    } 
</div>
@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<FoodItemCategory> categories = new List<FoodItemCategory>();

            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.

The database table was created with a unique PK and its complaining that you are adding a duplicate. I think it would help to see the table creation SQL and the context.SaveChanges() code. — Richard Brightwell, May 01 '11 at 19:28

score 4 · Accepted Answer · edited May 23 '17 at 10:24

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.

It seems as though Occam's Razor applies here. I did acknowledge that it was attempting to insert a duplicate record, and that I needed to code around that but didn't know if there was a standard way for ef to do this automagically but I guess not. Scaffolding can only go so far and in this case, the simplest answer (delete existing items and add them back) is the correct answer. Thanks so much Ladislav. — clockwiseq, May 01 '11 at 20:53
It does not seem to work for me: The line `context.Entry(item).CurrentValues.SetValues(foodItem);` throws a "Collection modified" exception because the collections of item and foodItem are not equal. — chiccodoro, Jun 29 '11 at 07:05
@chiccodoro: What collections are not equal? This method doesn't work with any collection, it only copies scalar values. — Ladislav Mrnka, Jun 29 '11 at 10:18
That's what I expected it to be, but it seems to care about collections, too. Otherwise I couldn't make sense of this exception message. — chiccodoro, Jun 29 '11 at 12:48
Excellent!!! Solved in minutes what i have spent several hours of hapless coding trying to work around..Thanks — Obi, Jan 16 '12 at 18:34

score 0 · Answer 2 · answered Jun 28 '11 at 09:49

In addition to Ladislav's answer, you can get rid of the Request[].split() part by using http://haacked.com/archive/2008/10/23/model-binding-to-a-list.aspx which makes:

[HttpPost]
public ActionResult Edit(FoodItem foodItem, ICollection<int> CategoryId)
{
    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();

        foreach (var id in CategoryID) 
        {
            // 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(id);
            // Now add category to attached food item to create new relation
            item.Categories.Add(category);
        }

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

    return View(foodItem);
}

Many-To-Many Relationship Basic Example (MVC3)

2 Answers2