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 FirstThe type or namespace name 'DbContext' could not be foundDropCreateDatabaseIfModelChanges Class
by
Evgeny. Also posted on
my website
