Implementing many-to-many relationships in MVC3

Just going to do a quick little example of how to implement Many to many relationships in MVC3, basically because the information available out there is pretty rubbish. We’re going to do the classic scenario: Products or product types posessing categories. I’m going to skip over what I consider to be the “bits that you probably don’t need help with” and focus directly on the problem. Below is the tidyest solution I can think of without having to include any third party or non-core assemblies etc. Please: If you can think of a better way, let me know!

So what we’re attempting to do is to implement a page which will allow the user to basically manage their categories. So, when they select a product type, they are going to be presented with a list of checkboxes (one for each category). They tick/untick these and then hit “submit”, and the product gets assigned those categories. This gives each product many categories, and each category many products. The fact that the info out there at time of writing is pretty poor staggers me given that this will be something that almost EVERY developer will need to do!

Technologies used: MVC3, Razor View Engine, C#, Linq to SQL / entity framework, SQL.
Patterns used: ViewModel, MVC, Repository.

The intention of this post isn’t to go through each line of code line by line; I’m just going to post how I did it and hopefully you can follow the code yourself. Having said that, I would be happy to answer any questions anyone has.

Database schema

Is pretty standard. Just the two tables we’re trying to relate and a link table containing the Primary Keys from each to represent a relationship. The rest of the code in this example assumes you’ve used the Entity framework to infer classes from these tables.

The View Model

So first things first, we need to create a view model (the model that will back up our view). We begin thinking about this by thinking about what is going to be on our page; basically a list of categories appropriate to a product type. So we’re only going to need two properties (ProductType and a dictionary of Categories, which will be the checkboxes when rendered). The View Model is therefore as follows;

    public class AssignProductTypeCategoriesViewModel
    {
        //The two properties
        public ProductType ProductType { get; private set; }       
        public Dictionary<Category, bool> CategoriesChecklist  { get; private set; }

        //a constructor to create the above two properties upon instantiation
        public AssignProductTypeCategoriesViewModel(ProductType productType, IEnumerable categories) 
        {
            bool found;
            ProductType = productType;
            CategoriesChecklist = new Dictionary<Category, bool>();

            foreach (var category in categories)
            {
                found = false;
                foreach (var producttypecategory in productType.ProductTypesCategories)
                {
                    if (category.ID == producttypecategory.CategoryId)
                    {
                        CategoriesChecklist.Add(category, true);
                        found = true;
                        break;
                    }                        
                }
                if(!found)
                    CategoriesChecklist.Add(category, false);
            }
        }
    }

Both property classes (Category and ProductType) as well as the link table class (ProductTypesCategories) were generated by the Linq to SQL classes generator / Entity Framework, which if you’ve named your tables differently to me will also be different.

The controller

Two actions: one to display the current product type-to-category setup, the other to handle the form once it’s posted back. Note that in the second one we are taking a form collection and the id of the product type we were editing. This is because there doesn’t appear to be a tidy way to bind the form straight to the model without sacrificing web accessibility/standards in the process. There are calls to repository methods in this code, will cover them in the next section.

        public ActionResult AssignProductTypeCategories(int id)
        {
            var productType = productsRepository.GetProductTypeById(id);
            var categories = productsRepository.GetAllCategories();
            var model = productsRepository.GetAssignProductTypeToCategoryModel(productType, categories);
            return View(model);
        }

        [HttpPost]
        public ActionResult AssignProductTypeCategories(int id, FormCollection postedForm)
        {
            List categoriesToAdd = new List();
            ProductType productType = productsRepository.GetProductTypeById(id);
            foreach (var category in productsRepository.GetAllCategories())
            {
                if (postedForm[category.Name].ToString().Contains("true"))
                {
                    categoriesToAdd.Add(category);
                }
            }

            productsRepository.ReassignCategories(productType, categoriesToAdd);
            productsRepository.Save();
            return RedirectToAction("ProductTypes");
        }

The Repository Methods

Here are the methods that were called in the last example. I haven’t bothered including “Save”, or any of the other query type methods as I imagine you’ve pretty much got those covered.

//This method just returns the view model (abstracted here for cleaner Controller code)
public AssignProductTypeCategoriesViewModel GetAssignProductTypeToCategoryModel(ProductType productType, IEnumerable categories)
        {
            return new AssignProductTypeCategoriesViewModel(productType, categories);
        }

//This method creates the new category to product configuration
        public void ReassignCategories(ProductType productType, List categoriesToAdd)
        {
            db.ProductTypesCategories.DeleteAllOnSubmit(productType.ProductTypesCategories);

            List relationshipsToAdd = new List();

            foreach (var category in categoriesToAdd)
            {
                relationshipsToAdd.Add(new ProductTypesCategory(productType, category));
            }

            db.ProductTypesCategories.InsertAllOnSubmit(relationshipsToAdd);
        }

One thing to note here is that the reassign method deletes the current configuraiton and replaces it with the new one. This is absolutely fine for this demonstration and probably for most cases (as every category will be considered every time they’re edited). The only risk you’d run in larger applications would be users “stomping on each others configurations” if they both happened to be changing the category setup at the same time.

And finally, the View

Strongly typed to the viewmodel we set up at the beginning. The only issue I’ve had is that the view engine adds some superfluous markup (hidden checkboxes) to the page, and these come back in the form collection that the controller handles. I have absolutely no idea why it does this, but I tried the Checkbox HTML helper outside of this example and it does it everywhere.

@model MirrorExpert.Models.ViewModels.AssignProductTypeCategoriesViewModel

@{
ViewBag.Title = "Assign Product Type Categories";
}

<h2>Assign Product Type Categories</h2>

@using (Html.BeginForm(new { id=Model.ProductType.ID }))
{
<fieldset>
<legend>@Model.ProductType.Name</legend>

@foreach (var category in @Model.CategoriesChecklist)
{
@Html.CheckBox(category.Key.Name, category.Value)
@Html.Label(category.Key.Name)
<br />
}

<p>
<input type="submit" value="Save" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "ProductTypes")
</div>

Which essentially renders like this with the categories and product types in my database;

This is a pretty tight solution for achieving this sort of functionality, but I would welcome any criticism or comments that people might have.

Advertisements

7 thoughts on “Implementing many-to-many relationships in MVC3

  1. Thanks for this great post. It was definitely helpful getting me going. However, after running through it I decided there were slightly easier ways to do it. First of all, you can save a column of data in your database if you don’t have an ID in your ProductTypesCategory table. Combine both the other two fields to be the primary key and when Entity Framework pulls it in, that table will basically be invisible. You don’t even have to worry about the relationshipsToAdd list in your repository method.

    I followed this link. The author posts in his comments how to do the many-to-many add/edits.
    http://blogs.msdn.com/b/alexj/archive/2009/06/19/tip-26-how-to-avoid-database-queries-using-stub-entities.aspx

    Thanks!

  2. Hi,
    thanks for your post, i think this would be really helpful, but what about when one uses code first approach? how do u resolve the productTypesCategory table.

  3. I am at the entry of MVC 3 and EF, but faced with an exact situation which requires a solution similar to this article. But I cannot figure out which code goes where? Any help?

  4. Mikey,

    Thanks for sharing the code, have you ever rewritten the code with the stubentities? If so could you post the code? I’m also looking for a way to implement a many to many relationship from an SQL db to MVC3

    Borrie

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s