ASP.NET MVC, Entity Framework, Modifying One-to-Many and Many-to-Many Relationships

Building on my previous article, which looked at adding data with one-to-many and many-to-many relationships via the Entity Framework, here's a look at how to modify that data within an ASP.NET MVC application.

To recap, I am in the process of migrating this site from ASP.NET Web Forms to the MVC platform. Along with the change in server-side approach, I am also applying the Entity Framework to help with my data access layer. The previous article looked at functionality within the content management system to add articles to the site, which includes applying an Article Type (one-to-many relationshhip) and Category tags (many-to-many). The Entity diagram is as follows:

 

Within the database, there is a bridging table between Articles and Categories (ArticleCategories). This contains just the ArticleID and CategoryID as foreign keys. A Composite Primary Key has been created on ArticleCategories which includes both of the fields in the table. Entity Framework needs a unique constraint to work nicely, and this makes sense. It means that I cannot have two entries where the same article is linked with the same category. If such a constraint were not put in place, EF will make the table read-only.

The workflow for modifying an existing Article entry is straightforward - I will present a list of Categories, and once one of those is selected, I will be given a list of article titles that appear in that category. Selecting one of those will present the article itself in an editable form, and a button for submitting the changes. Dependant, or cascading select lists immediately click my AJAX switches, so I will use jQuery to do this. The EditArticle View therefore contains references to jQuery, and the SelectBoxes plugin that featured in this article:

  

<script type="text/javascript" src="../../scripts/jquery-1.3.2.js"></script>
<script type="text/javascript" src="../../scripts/jquery.selectboxes.min.js"></script>

.........

<h2>
    Edit Article</h2>
  <div>
    <%=Html.DropDownList(
      "CategoryID", 
      new SelectList(
        (IEnumerable<Category>)ViewData["categories"],
        "CategoryID", 
        "CategoryName"
        ),
        string.Empty)%>
  </div>
  <div>
    <select name="ArticleID" id="ArticleID">
    </select>
  </div>
  <div id="articleform">
  </div>

There are two Select Lists - one built via an HtmlHelper extension method, and an empty one in html (ArticleID). There is also an empty div called articleform, which will get populated by an edit form via AJAX. The first Select list is populated with data by the Controller when the view is first requested. The controller action takes no arguments and just gets data from the Repository to list the Categories:


[ValidateInput(false)]
public ActionResult EditArticle()
{
  ViewData["Categories"] = repository.GetCategoryList();
  return View();
}

And the Repository method that gets called:


public IQueryable<Category> GetCategoryList()
{
  return (de.CategorySet.OrderBy(c => c.CategoryName)).AsQueryable();
}

All quite easy so far, and the result when running the page is this (as you would expect):

We need some AJAX help to get the list of articles when one of the Categories is selected, and to display them into the second select list. We also need some code on the server-side to respond to the AJAX request, so that involves another Action on the controller, and a data access method in the Repository. The controller action will return JSON so that we can work with it easily within jQuery:


public JsonResult GetArticlesByCategory(int categoryid)
{
  return Json(repository.GetArticlesByCategory(categoryid));
}

And the Repository method is as follows:


public IQueryable<ArticleTitle> GetArticlesByCategory(int id)
{
  return (de.ArticleSet.Where(a => a.Categories
                                     .Any(c => c.CategoryID == id))
    .OrderByDescending(a => a.DateCreated)
    .Select(a => new ArticleTitle
                   {
                     ID = a.ArticleID,
                     Head = a.Headline,
                   }))
    .AsQueryable();
}

If you have read previous ramblings of mine about migrating across to MVC and EF, you will already know that I created a small class called ArticleTitle, which just contains two properties - the Article Title and its ID. This means I can list articles without having to bring back all of the text content, date created, etc etc which I don't need to display. This lightweight class is perfect for the second select list. So I have a method that gets the items, and an action that turns the result into JSON. Now for some jQuery to fit the two together:


<script type="text/javascript">
  $(document).ready(function() {
  $("#CategoryID").change(function() {
  $.ajaxSetup({ cache: false });
      $.getJSON("/Admin/GetArticlesByCategory/" + $("#CategoryID").val(), null, function(data) {
        $("#ArticleID").removeOption(/./).addOption("", "", false);
        for (var i = 0; i < data.length; i++) {
          var val = data[i].ID;
          var text = data[i].Head;
          $("#ArticleID").addOption(val, text, false);
        }
      });
    });
    $("#ArticleID").change(function() {
    $("#articleform").load("/Admin/GetArticleForEdit/" + $("#ArticleID").val());
    });
  });
</script>

This goes into the <head> area of the EditArticle view. It applies an event handler to the onchange event of the Categories select list, which fires an AJAX request to the controller action just detailed. Before it does that, an ajaxSetup option is set to prevent IE from caching the results in the select list. Having obtained the JSON from the controller action, the SelectBoxes plug in clears the ArticleID select list of any data that was populated by a previous request, and then adds an empty string as a default option, before iterating over the JSON and populating the ArticleID select list. Finally, it adds an event handler to the onchange event of the ArticleID select list, which loads the result of a call to another controller action - GetArticleForEdit, which takes the ID of the article as a parameter:


public ActionResult GetArticleForEdit(int id)
{
  var article = repository.GetArticle(id);
  var selectedvalue = article.ArticleTypes.ArticleTypeID;
  ViewData["ArticleTypes"] = new SelectList(
                                         repository.GetArticleTypes(), 
                                         "ArticleTypeID", 
                                         "ArticleTypeName", 
                                         selectedvalue
                                         );
  ViewData["Categories"] = repository.GetCategoryList();
  ViewData["Article"] = article;
  return View("EditArticlePartial");
}

The action adds some data to the ViewDataDictionery - a SelectList populated with SelectListItems for the Select lsit of article types, together with the current Article's ArticleTypeID as the selected item (the last argumanet in teh parameter list), a list of categories and the details of the article to be edited, and returns it to a Partial View, EditArticlePartial, which then makes use of the data to provide the html for a populated edit form. the repository.GetArticle() method deserves a quick look:


public Article GetArticle(int id)
{
  return (de.ArticleSet
    .Include("ArticleTypes")
    .Include("Categories")
    .Where(a => a.ArticleID == id))
    .First();
}

An Article object is returned together with its collection of ArticleTypes and its collection of Categories through the use of the Include() extension method. This ensures that the collections are populated and loaded. The string in the parameter to the Include() method is the navigation property that features in the Entity Diagram at the beginning of the this article. We need the collections so that we can map checked checkboxes and select lists' selected items back when the edit form is displayed:


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>

    <% var article = (Article)ViewData["Article"]; %>
    <h2>Edit Article</h2>
    <form action="EditArticle" method="post">
    <table>
    <tr>
      <td>Headline</td>
      <td><%= Html.TextBox("Headline", article.Headline, new { style = "width:500px"})%></td>
    </tr>
    <tr>
      <td>Abstract</td>
      <td><%= Html.TextArea("Abstract", article.Abstract, new { cols = "80", rows = "5" })%></td>
    </tr>
    <tr>
      <td>MainText</td>
      <td><%= Html.TextArea("MainText", article.Maintext, new { cols = "80", rows = "10" })%></td>
    </tr>   
    <tr>
      <td>Article Type</td>
      <td>
        <%= Html.DropDownList("ArticleTypeID", (IEnumerable<SelectListItem>)ViewData["ArticleTypes"])%>  
    </td>
    </tr> 
    <tr>
      <td>Article Categories</td>
      <td><% foreach (var item in (IEnumerable<Category>)ViewData["Categories"]){ %>
      <input type="checkbox" name="CategoryID" value="<%=item.CategoryID %>" 
           <% foreach(var c in article.Categories)
           {
             if(c.CategoryID == item.CategoryID)
             { %>
               checked="checked"
           <%} 
           }%>/>
           <%= item.CategoryName %><br />
           <% } %>
      </td>
    </tr>     
    <tr>
      <td><input type="hidden" name="ArticleID" value="<%=article.ArticleID %>" /></td>
      <td><input type="submit" name="action" id="action" value="Submit" /></td>
    </tr>
    </table>
    </form>


Given the lack of helpers for multiple checkboxes within the MVC Framework, I have resorted to a classic ASP style of code. It's quite simple: as each option element it written to the browser, its value os compared to the CategoryID values in the collection of Categories that come with the article that was passed in via ViewData. If a match is found, checked="checked" is added to the option as an attribute. I've spaced the code out here in the sample, but written it all in one line in the original. The reason for that is that the html that gets rendered will retain the linebreaks that occur as a result of formatting the code to a more readable form. Just like the old ColdFusion pages, where if you viewed source, you would see large chunks of white space where server-side code was embedded. The real answer is to write your own Html.Helper for the CheckBoxList.

You will notice in the example shown in the image that my articles contain html tags. If I click submit at the moment, I will get a YSOD (Yellow Screen of Death) telling me that potentially dangerouss values were posted. So I prevent that by attributing the controller action that will take care of updating the article with ValidateInput(false):


[ValidateInput(false)]
[AcceptVerbs("POST")]
public ActionResult EditArticle(Article article)
{
  var articleTypeId = Request.Form["ArticleTypeID"];
  var categoryId = Request["CategoryID"].Split(',');
  repository.EditArticle(article, articleTypeId, categoryId);
  return Content("Updated");
}

The final part of code to look at is the actual repository.EditArticle() method that persists the changed values in the database:


public void EditArticle(Article article, string articleTypeId, string[] categoryId)
{
  var id = 0;
  Article art = de.ArticleSet
                  .Include("ArticleTypes")
                  .Include("Categories")
                  .Where(a => a.ArticleID == article.ArticleID)
                  .First();

  var count = art.Categories.Count;
  for (var i = 0; i < count; i++)
  {
    art.Categories.Remove(art.Categories.ElementAt(i));
    count--;
  }
  foreach (var c in categoryId)
  {
    id = int.Parse(c);
    Category category = de.CategorySet.Where(ct => ct.CategoryID == id).First();
    art.Categories.Add(category);
  }

  art.Headline = article.Headline;
  art.Abstract = article.Abstract;
  art.Maintext = article.Maintext;
  art.DateAmended = DateTime.Now;
  art.ArticleTypesReference.EntityKey = new EntityKey(
                                             "DotnettingEntities.ArticleTypeSet", 
                                             "ArticleTypeID", 
                                             int.Parse(articleTypeId)
                                             );

  de.SaveChanges();
}

The first section of the code gets the article that is to be updated. Updating the many-to-many relationship that exists between the Article and its Categories is next. If I was using a stored procedure, I would include some SQL that simply removes the existing rows from the bridging table and replace them with whatever was posted as part of the updated article. This will execute no matter whether there were any changes to the categories or not. The Entity Framework appears to be a lot more clever than that. The code does indeed remove the categories from the article's collection and marks them for deletion. Then the code adds whichever categories were posted from the form. However, if you use Sql Profiler to check, you will see that a SQL command to delete and insert items into the ArticleCategories bridging table only occurs if there have indeed been any changes to the categories that the article was related to. Nevertheless, an SQL command is executed for each of the Category objects that are fetched befroe being added to the collection.

The rest of the code is mostly self-explantory. The items that could be edited in the form are updated, along with the DateAmended property. Finally, the relationship between the Article and the ArticleType is updated by setting the value of the key for the potentially changed ArticleType. An alternative method of updating a one-to-many relationship like this is to query for the ArticleType object that has the ArticleTypeID that was posted, and then applying that to the ArticleType collection of the Article::


int id = int.Parse(articleTypeId);
ArticleType at = de.ArticleTypeSet.Where(a => a.ArticleTypeID == id).First();
article.ArticleTypes = at;

However, doing this causes an SQL query to be executed against the database. Setting the EntityKey value does not.

The result is that it all works. But I'm not 100% happy with the way that the many-to-many relationship is updated. I will find some time to do some more investigation - unless someone comes up with a cleaner way to manage this element of the process in the meantime.

Date Posted: Sunday, June 28, 2009 9:37 PM
Last Updated: Friday, October 10, 2014 9:10 PM
Posted by: Mikesdotnetting
Total Views to date: 77280

9 Comments

Saturday, August 1, 2009 10:07 AM - Rahul Gupta

colud u please send me this applictaion code

Thursday, August 27, 2009 4:06 PM - Jim

I'm working on a many to many problem and this is very helpful, except I'm new to MVC, JQuery and Ajax. Any chance you would send the application code?

A suggestion would be to add links where you reference other posts so you can easily follow to the post you refer to.

Thanks for the post. It has been very helpful.

Saturday, September 12, 2009 11:22 AM - venkateshsan

I want this full source code please send me my mail id

Saturday, September 12, 2009 8:56 PM - Mike

@ Rahul, Jim and venkateshan, and anyone else who asks:

All the code that is relevant to the article is in the article. I've done my best to try to simplify the concepts. If that is not enough, then I'm sorry. But I will not be making the source code of this application available.

Thursday, November 5, 2009 6:37 PM - Me12

This method to set relationships doesn't work when the object is passed to a web service.

EntityReference dd = new EntityReference();
dd.EntityKey = DocumentDestination(documentDestination_ID).EntityKey;
document.DocumentDestinationsReference = dd;

Tuesday, June 29, 2010 7:14 PM - nairit

Please tell me what is the ArticleSet?

Monday, August 2, 2010 2:38 PM - ahmed Zubair

nice solution, can u please send me the whole project please ?

Saturday, November 20, 2010 9:27 AM - Max

I can't get it running. Could you please send me the project source code.

Saturday, November 20, 2010 9:37 AM - Mike

@Max and ahmed

All of the relevant code is included in the article with detailed explanations. If you are having problems adapting the concepts to your particular projects, I suggest you post a question to the forums at www.asp.net and explain clearly what you are trying to achieve, what you have done so far and what problems/error messages you get.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.