In this lesson, we'll learn how to create database relationships using the Entity framework. We'll add a one-to-many relationship between Items and Categories so that each Item belongs to a specific Category.

Adding a One-To-Many Relationship to To Do List

---

Before we get started, let's review one-to-many relationships. Let's say we have two classes, Team and Player. We can conceptualize a one-to-many relationship between Team and Player by recognizing that one Team has many Players in it, but a Player may only belong to one Team at a time.

We would integrate this one-to-many relationship into a database by making sure that each Player entry has a TeamId to denote which specific Team they belong to, "linking" the tables together. In this case, a Player entry can only have one TeamId value, because a Player can only belong to one team.

Updating the Database

First, let's update our to_do_list database to include a categories table. We'll also make updates so that we can establish a relationship between the categories and items tables.

• Add a CategoryId column of type int in the items table. Set the Default/Expression to 0, to avoid Null errors.

  • If you have existing data in your database, you will need to remove it or set the CategoryId column value to 0 for all items.

• Create a categories table.

  • Add CategoryId as a column. It should be an int, primary key, non null, and auto incrementing.
  • Add Name as a column. It should be a Varchar(255).

• Don't forget to hit Apply and confirm that the changes actually happen.

Updating the Context

First, we need to add a Categories DbSet to ToDoListContext.cs:

using Microsoft.EntityFrameworkCore;

namespace ToDoList.Models
{
  public class ToDoListContext : DbContext
  {
    public DbSet<Category> Categories { get; set; }
    public DbSet<Item> Items { get; set; }

    public ToDoListContext(DbContextOptions options) : base(options) { }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseLazyLoadingProxies();
    }
  }
}

Notice that we add the OnConfiguring method to enable lazy-loading.

Updating the Category Class

Next, we'll completely update the old version of our Category class (found in Models/Category.cs):

using System.Collections.Generic;

namespace ToDoList.Models
{
    public class Category
    {
        public Category()
        {
            this.Items = new HashSet<Item>();
        }

        public int CategoryId { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Item> Items { get; set; }
    }
}

Let's walk through this file together:

• A HashSet is an unordered collection of unique elements. We create a HashSet of Items in the constructor to help avoid exceptions when no records exist in the "many" side of the relationship. A HashSet is also more performant than a List. Note that a HashSet can't have duplicates. See the documentation for more information on HashSets.

• The Category class includes a public property called Items that will return all Items that belong to a category.

• We declare Items as an instance of ICollection, a generic interface built into the .NET framework. As detailed in the documentation, an interface is essentially a collection of method signatures bundled together. Interfaces are often likened to "contracts" the developer "signs" because whenever a class extends an interface it must include every method outlined in the interface. ICollection is specifically a generic interface, which means it contains a bundle of different methods meant to work on a generic collection.

• We use ICollection specifically because Entity requires it. ICollection outlines methods for querying and changing data, which is functionality Entity needs to work the "ORM magic" preventing us from having to manually interact with our database like we do when using SQL directly.

• By declaring Items as an ICollection<Item> data type, we're ensuring Entity will be able to use all the ICollection methods it requires on the Item objects in order to act as our ORM.

• Notice that the Items property is being declared as virtual. As we covered in the last lesson, this will allow Entity to use lazy loading to load only the necessary resources from the database.

Adding a Controller and Views

Next, we need to update our CategoriesController and replace the CRUD actions with our new Entity-backed ones. This controller will look like the ItemsController we completed in the last lesson. Because we've already covered this functionality, take the opportunity to practice building out this controller and its corresponding views on your own. You'll get a chance to do this for this section's multi-day project. Use the Categories_Index view to display a list of categories. In order to see the CRUD functionality in action, let's go ahead and add a link in the homepage (Home/Index.cshtml) to go to our categories index view.

...
<p>@Html.ActionLink("See all categories", "Index", "Categories")</p>

Let's make sure to also add a link to the homepage in the Categories/Index.cshtml and Items/Index.cshtml pages

<p>@Html.ActionLink("Home", "Index", "Home")</p>

Now that we've created CRUD functionality and the respective views for categories, let's go ahead and implement the Category to Item relationship into our application.

Updating the Item Class

Finally, we need to update the Item class to set up its new relationship to Category:

namespace ToDoList.Models
{
    ...
    {
        ...
        public int CategoryId { get; set; }
        public virtual Category Category { get; set; }
    }
}

• Since each Item will be associated with a Category, the Item class now has a CategoryId property. We have also added a virtual Category property.

Updating the ItemsController

Let's update our ItemsController so that whenever an Item is loaded, its corresponding Category is available as well. Instead of using lazy loading, we're using eager loading here. Eager loading means that all information related to an object should be loaded. We don't need to add code that explicitly states what should be loaded.

We can utilize eager loading by using Entity's built-in Include() method. We'll make a small update to the Index() action in our ItemsController:

...
public ActionResult Index()
{
    List<Item> model = _db.Items.Include(item => item.Category).ToList();
    return View(model);
}
...

This basically states the following: for each Item in the database, include the Category it belongs to and then put all the Items into list.

Why are we using eager loading here if lazy loading is generally more efficient? Each Item has only one Category so there is a minimal amount of additional loading happening when we get information about an Item. This is in stark contrast to loading a Category, where there could potentially be many thousands of Items, particularly in a large enterprise application. We wouldn't want to load that huge list every time we access a Category. This is unique to our one-to-many relationship setup.

Updating the Index View

We'll format the list as a table in the Index view to keep it organized. Our table will display information about both Items and their associated Category.

@{
  Layout = "_Layout";
}

@using ToDoList.Models;
@model List<ToDoList.Models.Item>;

<h1>Items</h1>

@if (@Model.Count == 0)
{
  <h3>No items have been added yet!</h3>
} 

@foreach (Item item in Model)
{
  <li>@Html.ActionLink($"{item.Description}", "Details", new { id = item.ItemId }) | @item.Category.Name</li>
}

<p>@Html.ActionLink("Add new item", "Create")</p>

Our index view will now show both an Item and its related Category. At this point, we've set up the database to deal with the one-to-many relationship between Items and Categorys. However, there is still one key thing missing. There's no way for users to actually make the association between an Item and a Category in our application yet. In other words, we've set up the READ functionality for an association in our index view but users can't actually CREATE associations yet. In the next lesson, we'll update the rest of our methods to add this functionality and learn about a property of the View object called ViewBag.