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

Taking on two new technologies - ASP.NET MVC and the Entity Framework - at the same time was bound to provide some "interesting" moments. Getting data out via EF has so far been pretty straightforward. But when it got to building the back end of my site, I had some fun trying to manage INSERTS with One-To-Many and Many-To-Many relationships.

If you haven't been keeping up so far, my site is pretty simple with a fairly straightforward data model. I have Articles that belong to one Articletype, and can belong to many Categories. The data model is still the same as previously:

When I add a new article, I need to select one ArticleType (my types are Article, FAQ, Cheat Sheet etc) and apply one or more Categories. I will apply ASP.NET MVC to this one, and probably add Entity Framework as a new category too.

I've added an AdminController to the site which will house all interaction management involving the various admin-related tasks. At the moment, it only has three methods:


using System.Web.Mvc;
using MikesDotnetting.Models;

namespace MikesDotnetting.Controllers
{
  public class AdminController : Controller
  {
    private IAdminRepository repository;

    public AdminController() : this(new AdminRepository()){ }
    

    public AdminController(IAdminRepository rep)
    {
      repository = rep;
    }


    public ActionResult Index()
    {
      return View();
    }


    public ActionResult AddArticle()
    {
      ViewData["ArticleTypes"] = repository.GetArticleTypes();
      ViewData["Categories"] = repository.GetCategoryList();
      return View();
    }


    public ActionResult AddArticle(Article article)
    {


      return View("Index");
    }
  }
}

The Index method is the one that gets called first, and presents the default view, which will contain links to the various pages that form the mini-content management system I am building. There are two versions of the AddArticle() method. The first one has no parameters and is called as a result of clicking the Add Article link in the Index view. It will present the form for entering a new article. Tewo items are added to the ViewData dictionary - a collection of ArticleTypes and a collection of Categories. These will be used to allow me to select the ArticleType for the article and to apply Categories to it.

I'm showing all of the code for the AddArticle view because there were a few Gotchas I came across while working with it:


<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Admin.Master" 
                                        Inherits="System.Web.Mvc.ViewPage" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <title>AddArticle</title>
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">

    <h2>AddArticle</h2>
    <% using (Html.BeginForm()){ %>
    <table>
    <tr>
      <td>Headline</td>
      <td><%= Html.TextBox("Headline", null, new { style = "width:500px" }) %></td>
    </tr>
    <tr>
      <td>Abstract</td>
      <td><%= Html.TextArea("Abstract", new { cols = "60", rows="7" }) %></td>
    </tr>
    <tr>
      <td>MainText</td>
      <td><%= Html.TextArea("MainText", new { cols = "60", rows="15" }) %></td>
    </tr>   
    <tr>
      <td>Article Type</td>
      <td><%=Html.DropDownList(
      "ArticleTypeID", 
      new SelectList(
        (IEnumerable<ArticleType>)ViewData["ArticleTypes"],
        "ArticleTypeID", 
        "ArticleTypeName" 
        ))%></td>
    </tr> 
    <tr>
      <td>Article Categories</td>
      <td><% foreach (var item in (IEnumerable<Category>)ViewData["Categories"])
             { %>
      <input type="checkbox" name="CategoryID" id="CategoryID" value="<%=item.CategoryID %>" />
      <%= item.CategoryName %><br />
      <% } %></td>
    </tr>     
    <tr>
      <td></td>
      <td><input type="submit" name="action" id="action" value="Submit" /></td>
    </tr>
    </table>
    <% } %>
</asp:Content>


Gotcha 1- came when adding a MasterPage for the Admin area. It is very obvious now, and I feel a right plonker even admitting this, but when I went to Add New Item, I selected Master Page. I should have selected MVC View Master Page instead. The result was having to mess around rearranging ContentPlaceHolders and removing Form runat="server" tags. It was a few days later that I discovered the MVC appropriate option. Years of Web Forms habit creeping in, so it might be something you need to watch for...

You might notice the volume of code that's used to set up the DropDownList in the View. There is another way to do this, and that is to change the AdminController AddArticle() method that gets the Categories for the ViewData to this (lines broken for display purposes):


public ActionResult AddArticle()
{
  ViewData["ArticleTypes"] = new SelectList(
                                    repository.GetArticleTypes(), 
                                    "ArticleTypeID", 
                                    "ArticleTypeName"
                                    );
  ViewData["Categories"] = repository.GetCategoryList();
  return View();
}

And then to put this in the View:


<tr>
  <td>Article Type</td>
  <td><%=Html.DropDownList(
         "ArticleTypeID",
         (IEnumerable<SelectListItem>)ViewData["ArticleTypes"]
        )%></td>
</tr> 

It results in the same thing.

Gotcha 2 arose with the CheckBoxes. There is no CheckBoxList extension for HtmlHelper. This should have been no big deal, except that the Html.CheckBox() extension method has a rediculous byproduct of generating hidden fields having the same name and id as the visible checkbox input. After Googling a bit, I discovered that a CheckBoxList helper existed in Preview 5, but was removed. Fair enough - I can write my own. However, what's the point of the hidden fields? Apparently, they are there so that you can checked to see if a value was "checked" or not or establish whether the checkbox actually ever got rendered to the page. "What the smeg?" as WorldSpawn said. Why would it "not" get rendered? I've banged my brain on this and still cannot come up with anything sensible to explain the thinking there. Maybe someone can enlighten me? Is MVC so unstable that my form fields might just decide not to appear? Anyway, pre-Web Forms, normal web development involved checking for a comma-separated string in the Form collection when multiple inputs have the same name attribute. And that's exactly what I want to do (just like WorldSpawn in his post). Write my own HtmlHelper extension method or just resort to classic ASP style html and Response.Write()? I opted for the latter in this case.

Moving on, I now have my Add Article form all ready awaiting the first article to be added. Now I need ot amend the overloaded AddArticle action on the AdminController to work with the form inputs, and to call a method in the AdminRepository that will persist the new article in my database. At the moment, the method takes an Article object as a parameter. However, I have two properties on the Article object that are collections representing the relationships in the database for ArticleTypes and Categories. I need to take the values of the collection properties and handle them separately. So the AddArticle() action is amended as follows:


[AcceptVerbs("POST")]
public ActionResult AddArticle([Bind(Exclude = "ArticleID")] Article article)
{
  var articleTypeId = Request.Form["ArticleTypeID"];
  var categoryId = Request["CategoryID"];

  repository.AddArticle(article, articleTypeId, categoryId);

  return View();
}

Since the form is POSTed to the controller action, the appropriate AcceptVerbs attribute is applied. From there, the ArticleID value from the DropDown and the CategoryIDs from the checkboxes are extracted from the Request.Form collection, and passed together with the Article object to the Repository's AddArticle method. However, Gotcha 3 was discovered here. My articles contain html markup in their text. When submitting one of these, the application threw an exception: A potentially dangerous Request.Form value was detected...

I tried the advice in the Description part of the error message: "You can disable request validation by setting validateRequest=false in the Page directive or in the configuration section". I added ValidateRequest="false" both to the View and to the MasterPage, but neither prevented the exception. Looking at the Views web.config file, I found that validateRequest="false" is set within the pages section too. However, I also discovered a comment within the web.config as follows:


<!--
    Enabling request validation in view pages would cause validation to occur
    after the input has already been processed by the controller. By default
    MVC performs request validation before a controller processes the input.
    To change this behavior apply the ValidateInputAttribute to a
    controller or action.
-->

So the answer is to add another attribute to the controller Action:


[ValidateInput(false)]
[AcceptVerbs("POST")]
public ActionResult AddArticle([Bind(Exclude = "ArticleID")] Article article)
{
  var articleTypeId = Request.Form["ArticleTypeID"];
  var categoryId = Request["CategoryID"];

  repository.AddArticle(article, articleTypeId, categoryId);

  return View();
}

But that's not the end of the story. It appears that you only need to add this attribute if you are returning the View associated with the Action. If I change the ActionResult to return View("Index"), the ValidateInput() attribute is no longer required.

Now we will look at the AdminRepository method that actually handles the persisting of the new article:


public void AddArticle(Article article, string articleTypeId, string categoryId)
{
  int id = int.Parse(articleTypeId);
  ArticleType at = de.ArticleTypeSet.Where(a => a.ArticleTypeID == id).First();
  article.ArticleTypes = at;
  article.DateCreated = DateTime.Now;
  article.PostedBy = "Mikesdotnetting";

  var catids = categoryId.Split(',');
  foreach(var catid in catids)
  {
    id = int.Parse(catid);
    Category category = de.CategorySet.Where(ct => ct.CategoryID == id).First();
    article.Categories.Add(category);
  }

  de.AddToArticleSet(article);
  de.SaveChanges();
}

With plain ADO.NET code, managing one-to-many and many-to-many relationships is easy. For the One-To-Many relationship, you just get the Primary Key of the ArticleType and add it to the Articles table as a Foreign Key, thus associating the correct ArticleType with the Article. In v1.0 of the Entity Framework, things aren't so simple - although the next release changes this. For Many-To-Many relationships, you just iterate through the CategoryIDs that were submitted as part of the Form collections and insert them with the newly obtained ID of the Article into hte bridging table that sits between Articles and Categories. With EF, you need to get the full ArticleType object, and add it to the Article object's ArticleType collection property. The same with Categories. So, I've taken the string that was posted in the FormCollection that represents the ArticleTypeID and converted it to an int. I have then obtained an ArticleType object via EF that matches the ID, and added that object to the Article.ArticleTypes property.

The Category collection is handled in the same way. First, the comma-separated string is converted to an array using the String.Split() method, and then each individual array element is converted to an int type before being used in the LINQ "look-up" to get the full Category object to be added to the Categories collection of the Article.

Gotcha 4: I've touched on it before, but Many-To-Many relationships within databases are usually handled by a bridging table. In my case, the Articles and Categories tables are joined through an ArticleCategories table, which consist of two fields: ArticleID and CategoryID. These are both Foreign Keys which link back to the Primary Key of the respective parent tables. This linking table is not shown in the visual representation that the EDMX produces (above). However, just running the code at the moment will lead to an exception:

"Unable to update the EntitySet 'ArticleCategories' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation."

When you look at the EDMX in Design View, you may also see the following warning:

"The table/view ''ArticleCategories' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view."

What this basically means is that the Entity Framework cannot ensure that duplicate entries won't be created in the table, because there is no Contraint on the table to prevent them. If you add new column to the bridging table as a Primary Key, the Model diagram will explode into more One-To-Many relationships. So you need to create a Composite Key using the two existing columns. This is easily accomplished through Sql Server Management Studio - just open the table in Design mode, and then, holding the Ctrl key down, select both columns and click the golden key icon in the top menu. Job done. A Primary Key has been added that applies the No Duplicates constraint and EF is happy again. You will need to rebuild the Model, and you may even have to shut VS down to get rid of the warning message (which happened to me).

Once fixed, everything works as expected.

You may wonder why I have no validation in place. While you should generally validate all user input, in this case, I will be the only person using the Add Article functionality. No one adds articles on my site except me. Consequently, I know exactly which fields are mandatory, and what the data types should be. In short, I don't need it in this instance.