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

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.