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.

Date Posted: Wednesday, June 17, 2009 9:55 PM
Last Updated: Friday, October 10, 2014 9:11 PM
Posted by: Mikesdotnetting
Total Views to date: 134340

17 Comments

Friday, June 19, 2009 1:11 PM - Anthony Herring

I'm just learning ASP.NET MVC with EF, and your articles are very helpful. Thank you and please don't stop!

-Tony

Friday, July 17, 2009 4:05 PM - Pierre

Hi,

I'm learning it too. I did a many to many Association in my model. Like the one between Article and Category. For me it's Movie and Actors. When I read a movie the Actor collection contained in Movie is alway empty. But I have data. It should contains Actor objects..

here is the Actors EntityCollection from my the Movie entity.


[global::System.Data.Objects.DataClasses.EdmRelationshipNavigationPropertyAttribute("MoviesModel", "CastIn", "Actor")]
[global::System.Xml.Serialization.XmlIgnoreAttribute()]
[global::System.Xml.Serialization.SoapIgnoreAttribute()]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public global::System.Data.Objects.DataClasses.EntityCollection Actors
{
get
{
return
(global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.GetRelatedCollection("MoviesModel.CastIn", "Actor");
}
set
{
if ((value != null))
{
((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.InitializeRelatedCollection("MoviesModel.CastIn", "Actor", value);
}
}
}

Can you help me?

Wednesday, August 26, 2009 9:20 AM - Guido

The checkbox thing: The trouble is that browsers only ever send the value of a checkbox in a POST if it is selected. So if you have a checkbox named "checkie", and the user has it selected, whatever you chose as the checkbox value (let's say, "true") gets sent to the server as checkie=true.

However, if the user does not select it, there will not be any value named "checkie" be sent to the server.

This is a problem if you want to overwrite values with the checkbox, because the entity framework only saves changed values - and a value that is not there does not change nor overwrite anything. Unless you want redundant code in each of your controller that handles boolean values, you will need an explicit "checkie=false" sent to the server for easy handling of these overwrites.

What we want is, if the checkbox is not selected, there should be checkie=false sent to the server. And that's what the hidden field is for: It is overwritten by checkie=true if the checkbox itself if that is checked, otherwise it sets checkie=false.

I am not surprised the documentation is not perfect there. Not sure if the programmers knew what they were doing. I'm programming Ruby on Rails, and the whole ASP.NET MVC framework is pretty much a copy of that. Without automatic model roundtrip editing, with way less convenient reflection, and generally more clumsy.

Wednesday, August 26, 2009 12:54 PM - Mike

@Guido

I get the thinking behind why the MVC team did what they did. My problem is that it's messing around with the HTML specs, and produces unexpected behaviour. The Html.Helper in this case doesn't act in accordance with the specs. I don't have any problem being able to handle the absence of a value - especially as that is what I would have expected to happen in a checkbox was not checked. But I am now forced to handle the presence of one when I was not expecting one.

I am not familiar with RoR, although I have read a few times that it is the inspiration for ASP.NET MVC. I expect the ASP.NET team are working hard ot catch up ;o)

Tuesday, September 1, 2009 4:39 PM - coolcnid

About the checkbox helper, I think if you do not have the hidden field, then you can have two situations:
1. a normal input:checkbox as you did, this would postback on/off, not true/false
2. input:checkbox value="true/false", this would possible to postback null
with the input:hidden, you get at least something?

Sunday, October 18, 2009 8:58 AM - sri

Hi,
I followed your article and created a '1 to m' relationship between category and product tables

After generating entities, i tried do the following from controller code

category cat = new category();
cat.catid = 1;
cat.catname = "tennis";
List prods = cat.product.ToList();
I expected the prods to have two entries as my tables have 2 rows in product table corresponding to category 1.
However, my prods list is empty. Why?


Following is the snippet of code from my model class for category

[global::System.Data.Objects.DataClasses.EdmRelationshipNavigationPropertyAttribute("shoppingModel", "FK_product_category", "product")]
[global::System.Xml.Serialization.XmlIgnoreAttribute()]
[global::System.Xml.Serialization.SoapIgnoreAttribute()]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public global::System.Data.Objects.DataClasses.EntityCollection product
{
get
{
return ((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.GetRelatedCollection("shoppingModel.FK_product_category", "product");
}
set
{
if ((value != null))
{
((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.InitializeRelatedCollection("shoppingModel.FK_product_category", "product", value);
}
}
}
}

Am i missing anything here?
Please help me as i need this to be resolved pretty quickly and move on to other tasks.
Thanks again
sri

Wednesday, November 11, 2009 7:41 PM - Sue Brandom

i am recreating this example and am having a prolbem implementing the IAdminRepository for the AddArticle. this is what i have
public interface IAdminRepository
{
IEnumerable GetArticleTypes();
IEnumerable GetCategoryList();
Article AddArticle(Article article, string articleTypeId, string categoryId);
}

Is this correct? The problem is with the AddArticle does not have the matching return type of of MikesDotnetting.Models.Article

Thank you for your posting, one of few on the web that addresses many to many in MVC.
Sue

Wednesday, November 11, 2009 8:46 PM - Mike

@Sue

The Interface should show a return type of void for the AddArticle method:
{
IEnumerable GetArticleTypes();
IEnumerable GetCategoryList();
void AddArticle(Article article, string articleTypeId, string categoryId);
}

Saturday, June 19, 2010 8:09 AM - Sandeep Kumar

Hi....Thanks for your wonderful tutorials. I have a question. suppose call to one repository create method inserts/updates values in more than one table. Is there any way to use transactions in that scenario to ensure the values gets inserted/updated in all the tables and if not these get rolled back ?

Saturday, June 19, 2010 8:35 AM - Mike

@Sandeep

Google or another search engine will help you find articles covering Transactions and EF such as this one:

Wednesday, June 23, 2010 11:04 AM - Gautam Jain


Thanks Mike. Your article help me today.

Thursday, October 7, 2010 9:56 AM - Geewiz

Thanks dude, the many to many solution was very very very helpful.

Sunday, October 24, 2010 7:50 AM - Rodney

Nice article. Off topic question (if you don't mind) Did you use 3rd party to display ASP.NET code?

Thursday, April 28, 2011 1:51 PM - Quantum Developer

Excellent article!!! I believe the examples and explanations presented here were clearly stated and adequate. Continue the great work. :)

Saturday, March 10, 2012 12:14 AM - ozancan

thank you very much. you saved my night

Sunday, September 30, 2012 12:53 PM - Idrees Khan

I love u man. What a nice solution. It helped me a lot. Thanks for ur lovely article
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.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...