Lesson Weekend

In the last section, we created a to do list with a one-to-many relationship. However, what if we want an Item to be able to have multiple Categorys? We need to use a many-to-many relationship instead.

In this lesson, we'll see how we can establish this relationship in our database and how we can use C# objects to model this relationship.

Updating To Do List Models for Many-to-Many


Let's refactor our ToDoList project to represent a many-to-many relationship. If you need a reference of what the ToDoList project should look like at this point, reference this branch in the sample repo.

In the last section, we started by configuring our database and adding tables. We will no longer need to do that. Because we will use code first migrations with Entity, we will first create our models and then let Entity scaffold migrations based on those models. If you have a to_do_list database, right-click on the database in MySQLWorkbench and select Drop Schema. This will drop the database.

The following files will have the same setup as in the last section: Startup.cs, Program.cs, appsettings.json, and ToDoList.csproj. We will focus entirely on the models in this lesson, including ToDoListContext.cs, and then run our first migration.

Let's start with our models. Much of the code will look familiar but there will be some new code as well. Here's the Category model:

Models/Category.cs
using System.Collections.Generic;

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

        public int CategoryId { get; set; }
        public string Name { get; set; }
        public virtual ICollection<CategoryItem> JoinEntities { get; set; }
    }
}

Our model has both Name and CategoryId auto-implemented properties. This is the familiar part. Don't worry about the CategoryItem data type just yet. We'll explain that in a moment. Here is the new code:

public Category()
{
    this.JoinEntities = new HashSet<CategoryItem>();
}

public virtual ICollection<CategoryItem> JoinEntities {get; set;}

When we establish a many-to-many relationship between two classes, we need to include a navigation property. This is simply a property on one class that includes a reference to a related class. The example above is a collection navigation property because it contains a reference to many related Items. If the example above was a reference to just a single entity, as would be the case with a one-to-many relationship, it would be called a reference navigation property.

If we don't have this reference, we won't be able to access related Items in our controllers and views.

We'll make a similar update to our Item model:

Models/Item.cs
using System.Collections.Generic;

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

        public int ItemId { get; set; }
        public string Description { get; set; }

        public virtual ICollection<CategoryItem> JoinEntities { get;}
    }
}

Our Item class contains auto-implemented properties for ItemId and Description. It also contains a collection navigation property for JoinEntities, which will hold the list of relationships this Item is a part of -- which is how we will find its related Categories. Note that this property only has a getter method while the collection navigation property on the Category class has both a getter and setter. We could add a setter method to the JoinEntities property as well but we'll only be modifying the relationship between an Item and a Category via a Category's JoinEntities in our application. This will become more readily apparent once we build out our controllers and views.

We've updated both of our models and included collection navigation properties as well. However, we haven't actually created a relationship between the two classes yet. Let's create a class for the join table and then let Entity do the rest of the work.

The JOIN entity

We need to create a class that will hold information about the relationship between a Category and an Item. We'll call the class CategoryItem, which is an alphabetical combination of the two classes.

Models/CategoryItem.cs
namespace ToDoList.Models
{
  public class CategoryItem
    {       
        public int CategoryItemId { get; set; }
        public int ItemId { get; set; }
        public int CategoryId { get; set; }
        public virtual Item Item { get; set; }
        public virtual Category Category { get; set; }
    }
}

We have three different Id properties: one for CategoryItem, one for Item, and one for Category. In addition to that, we also have both Item and Category included as objects. Note that there is no constructor for this model. That's all we need for now. Entity will take care of the rest once we are ready to run our migration.

Configure the relationship

Now we need to set up our ToDoListContext.

Models/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 DbSet<CategoryItem> CategoryItem { get; set; }

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseLazyLoadingProxies();
    }
  }
}
  • Each DbSet we've included will become a table in our database. We've previously declared both Categories and Items DbSets, but now include a CategoryItem DbSet as well that represents our join table.

Design Time

Before we build our project and use Entity to create and run our migrations, we'll have to do a bit more configuration to our application. Create a file called DesignTimeDbContextFactory.cs inside of the Models folder with the following code:

Models/DesignTimeDbContextFactory.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
using System.IO;

namespace ToDoList.Models
{
  public class ToDoListContextFactory : IDesignTimeDbContextFactory<ToDoListContext>
  {

    ToDoListContext IDesignTimeDbContextFactory<ToDoListContext>.CreateDbContext(string[] args)
    {
      IConfigurationRoot configuration = new ConfigurationBuilder()
          .SetBasePath(Directory.GetCurrentDirectory())
          .AddJsonFile("appsettings.json")
          .Build();

      var builder = new DbContextOptionsBuilder<ToDoListContext>();

      builder.UseMySql(configuration["ConnectionStrings:DefaultConnection"], ServerVersion.AutoDetect(configuration["ConnectionStrings:DefaultConnection"]));

      return new ToDoListContext(builder.Options);
    }
  }
}

According to the official documentation:

At design-time, derived DbContext instances can be created in order to enable specific design-time experiences such as Migrations. Design-time services will automatically discover implementations of this interface that are in the startup assembly or the same assembly as the derived context.

We're familiar with the term run time, but what exactly is design time? It's just the time when we are actually writing and designing our code. Running migrations is something we do when we are designing our application, not when we are actually running our application.

Ultimately, we need the code above so that our application can properly find our database context.

See the documentation for more information.

For our project to recognize this context factory, we will also need to add a new package:

$ dotnet add package Microsoft.EntityFrameworkCore.Design -v 5.0.0

Build the database


In order to properly create our first migration, we'll have to make sure our application can successfully build. To do that, let's comment out the code in both ItemsController.cs and CategoriesController.cs.

Let's also go ahead and comment out the code in all of our views. Remember, to comment out blocks of code in cshtml files, we use @* Code to be commented *@. Some editors will not properly comment out the code in cshtml files when using the keyboard shortcut, instead using the HTML comment syntax.

Then, run dotnet build to check for errors. Once the project builds successfully, we can create and apply our first migration.

Remember that we need to run the command to create a migration in the root directory of the project:

$ dotnet ef migrations add Initial

If you get this error:

Could not execute because the specified command or file was not found.
Possible reasons for this include:
  * You misspelled a built-in dotnet command.
  * You intended to execute a .NET Core program, but dotnet-ef does not exist.
  * You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.

This means that you have not installed the dotnet ef tool. To fix this error run the following command to globally install dotnet ef tool:

$ dotnet tool install --global dotnet-ef --version 3.0.0

As we discussed in the last lesson, this will create a new Migrations folder in the root directory. This folder now holds three files. Feel free to take a look at the content of these files but do not worry about altering them. We will use the scaffold that Entity provides for our migration. When we run this command, Entity looks at all our models and then creates our migration based on that.

We are now ready to apply our migration:

$ dotnet ef database update

If we take a look in MySQL Workbench, our to_do_list database now has three tables: Categories, Items, and CategoryItem. In the CategoryItem table, there are three columns: CategoryItemId, ItemId and CategoryId. This is exactly what we want.

Repository Reference

Follow the link below to view how a sample version of the project should look at this point. Note that this is a link to a specific commit in the repository.

Example GitHub Repo for To Do List

Lesson 4 of 14
Last updated more than 3 months ago.