Sunday, May 13, 2012

Learning MVC - Code First and Many-to-many Relationship

Learning MVC I came across a need to create a many-to-many relationship between entities using the code first approach. For an example, let's consider the Recipes database which holds recipes and ingredients. A recipe has multiple ingredients, such as meat, potatoes, pepper and so on. At the same time an ingredient may belong to multiple recipes, i.e. you can cook meat with potatoes but fish and chips will also require potatoes. That's a classic many-to-many relationship which has a classic mapping table solution, where one many-to-many relationship would convert to two one-to-many by adding a new table. The diagram would look like the following:

Typical database solution for many-to-many relationship

So my first guess was that I will have to create three classes while implementing a similar structure with MVC code first. Fortunately, so far it appears to be easier than that. Below is the small exercise that creates a most basic MVC project from scratch and illustrates the many-to-many relationship via code first.

Start Visual Studio 2010 and select File -> New Project, select ASP.NET MVC 3 Web Application. On the next screen I selected Empty application to make the example most simple.

Create an empty MVC 3 application

After the project was created, I added a HomeController to create a basic home page. Probably not necessary, but lets the application run without displaying an error. Right-click Controllers folder, select Add -> Controller and add an empty HomeController.

Add a HomeController

Inside the HomeController.cs, the Index() method, right-click and select Add View. Leave default values and click Add.

Create an Index page for the project

Run the project to verify that no errors are displayed. Now it is the time to add entities for the Recipe and Ingredient. Right-click Models folder, select Add -> Class and call the class Recipe.cs. Add another one called Ingredient.cs. The little trick with a many-to-many relationship is to create an ICollection within each of the two related entities. The collection will actually hold those "many" entities that are related to the particular instance. In our case - the Recipe holds a list of all Ingredients it uses.

Add new class in Models folder

public class Recipe
{
 public int RecipeID { get; set; }
 public string Name { get; set; }

 public virtual ICollection<Ingredient> Ingredients { get; set; }

 public Recipe()
 {
  Ingredients = new HashSet<Ingredient>();
 }
}

public class Ingredient
{
 public int IngredientID { get; set; }
 public string Name { get; set; }

 public virtual ICollection<Recipe> Recipes { get; set; }

 public Ingredient()
 {
  Recipes = new HashSet<Recipe>();
 }
}

And we'll need to have some sample data to verify that the application is working as expected. So add another class and call it SampleData.cs. Entity Framework allows to "seed" the newly created database. An implementation of DropCreateDatabaseIfModelChanges class will re-seed the database when model changes. This is handy for testing.

public class SampleData : DropCreateDatabaseIfModelChanges<RecipesEntities>
{
 protected override void Seed(RecipesEntities context)
 {
  var ingredient0 = new Ingredient{Name = "Meat"};
  var ingredient1 = new Ingredient{Name = "Fish"};
  var ingredient2 = new Ingredient{Name = "Potato"};

  var ingredients = new List<Ingredient>(){ingredient0, ingredient1, ingredient2};

  ingredients.ForEach(i => context.Ingredients.Add(i));

  var recipes = new List<Recipe>();
  
  recipes.Add(new Recipe{Name = "Grilled fish with potatoes", Ingredients = new List<Ingredient>() {ingredient1, ingredient2}});
  recipes.Add(new Recipe{Name = "Grilled steak with potatoes", Ingredients = new List<Ingredient>() {ingredient0, ingredient2}});

  recipes.ForEach(r => context.Recipes.Add(r));
 }
}

Next step was to create the database. Right-click Project, select Add -> Add ASP.NET Folder -> App_Data. The folder will be created. It will already have the correct security access settings.

Create an App_Data folder

Next, I added the following at the end of the web.config file just before the closing "configuration" tag. Now the Entity Framework will know how to connect to the database:

<connectionStrings>
 <add name="RecipesEntities"
 connectionString="Data Source=|DataDirectory|Recipes.sdf"
 providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>

Next step is to create a context class, which will represent the Entity Framework database context. It is very simple indeed, and I'll create it by adding a new class called RecipeEntities.cs in the model folder. This class will be able to handle database operations due to the fact that it is extending DbContext. Here is the code:

public class RecipesEntities : DbContext
{
 public DbSet<Recipe> Recipes { get; set; }
 public DbSet<Ingredient> Ingredients { get; set; }
}

Now, with the model, database, context and some sample data in place, it is time to verify that data is actually displayed properly by the application. First, the recipes. I'm going to check that the list is displayed properly and that I can display all the details I'm interested in (for now, just the list of ingredients). For that, I'll need a RecipeController and two views, List and Details. First, the controller, that I will create with default methods for displaying, editing, creating and deleting data. I'm only interested in two methods, which I'll modify as follows

RecipesEntities recipeDB = new RecipesEntities();

// GET: /Recipe/
public ActionResult Index()
{
 var recipes = recipeDB.Recipes.ToList();
 return View(recipes);
}

// GET: /Recipe/Details/5
public ActionResult Details(int id)
{
 var recipe = recipeDB.Recipes.Find(id);
 return View(recipe);
}

Now I'll right-click within the Index method and select Add View, which I will configure as follows:

Create the List view

Now if I run the application as it is and navigate to Recipe (http://localhost/Recipe), I should see the list of recipes.

Display the list of ingredients

Next, I want to see the details. I'll add another view by right-clicking within the Details method of the controller and select Add View, which I will configure in the similar fashion:

Create the Details view

The default contents of the view will look similar to this:

@model RecipesSample.Models.Recipe
@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>
<fieldset>
    <legend>Recipe</legend>
    <div class="display-label">Name</div>
    <div class="display-field">@Model.Name</div>
</fieldset>
<p>
    @Html.ActionLink("Edit", "Edit", new { id=Model.RecipeID }) |
    @Html.ActionLink("Back to List", "Index")
</p>

This, however, will only display the name of the recipe, but not the ingredients. To check that the ingredients are returned from the database properly, I have to modify the view to look similar to this:

@model Recipes.Models.Recipe
@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<fieldset>
    <legend>Recipe</legend>
    <div class="display-label">Name</div>
    <div class="display-field">@Model.Name</div>
</fieldset>

<fieldset>
    <legend>Ingredients</legend>
    @foreach (Recipes.Models.Ingredient ingredient in Model.Ingredients)
    { 
        <div>@Html.DisplayFor(model => ingredient.Name)</div>
    }
</fieldset>

<p>
    @Html.ActionLink("Edit", "Edit", new { id=Model.RecipeID }) |
    @Html.ActionLink("Back to List", "Index")
</p>

Now if I run the application, navigate to Recipe and click Details on any of them (the link will point to http://localhost:49606/Recipe/Details/1 or similar), I will see the following page:

Display the related data

The recipe ingredients were successfully extracted from the model and displayed. As an exercise, it's easy to perform reverse action - check that if the ingredient is displayed, the recipes where it is used can also be shown. Hint: the view code may look similar to this:

@model Recipes.Models.Ingredient
@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<fieldset>
    <legend>Ingredient</legend>
    <div class="display-label">Name</div>
    <div class="display-field">@Model.Name</div>
</fieldset>

<fieldset>
    <legend>Is used in the following recipes</legend>
    @foreach (Recipes.Models.Recipe recipe in Model.Recipes)
    { 
        <div>@Html.DisplayFor(model => recipe.Name)</div>
    }
</fieldset>
<p>
    @Html.ActionLink("Edit", "Edit", new { id=Model.IngredientID }) |
    @Html.ActionLink("Back to List", "Index")
</p>

And the output will be

Display the related data

This is a very crude example without following "best practices" (such as creating the DbContext inside the using statement) and without any formatting, but it shows that an application that employs the many-to-many relationships between the entities can be created with MVC with just several lines of code.

References I used:

Part 4: Models and Data Access
Creating a Many To Many Mapping Using Code First
The type or namespace name 'DbContext' could not be found
DropCreateDatabaseIfModelChanges Class by . Also posted on my website

5 comments:

Dean Borosevich said...

The problem with this tutorial is that you are ignoring the biggest issue - how to do many-to-many with a "payload" in the join table. In the first picture on your tutorial, you'll see that you have "amount" in the join table. But you don't show how to implement this. The way you've implemented it, you cannot have extra information. (Not an easy task).

Modem56k said...

did anyone ever post anything that accomplished what dean pointed out. Im looking into added an additional column to the join table but have yet to figure this out.. can anyone point me in the right direction?

Ng Peter said...

I understand all the steps except for this line:

@foreach (Recipes.Models.Ingredient ingredient in Model.Ingredients)

Can you kindly explain?

I also want to display the ingredients in the Index page, under each respective ingredient using nested foreach statement. How should I do it?

Ng Peter said...

I also want to display the ingredients in the Index page, under each respective recipe using nested foreach statement. How should I do it?

Ng Peter said...

I think I have figured out:

1. Meaning...Ingredient Object for each ingredient.

2. For the Index.cshtml, just nest similar code inside the @foreach.