Creating a Many-to-Many Relationship

Let’s set up a many-to-many relationship between Event and Tag.

Join Tables

To relate data in a many-to-many fashion in a relational database requires a new type of SQL table.

One-to-many relationships are established at the database level by the use of a foreign key column on one side of the relationship. Our Events table has a foreign key column: CategoryId.

For each Events row, the column CategoryId contains the Id of a Categories row. This is the primary key of the row in Categories that the Events row is related to. The Event/EventCategory relationship is many-to-one, so many event rows may have the same CategoryId value.

Using foreign and primary keys to create many-to-many relationships is a bit trickier. In order to relate rows in Events to rows in Tag, we need need a third table, known as a join table. A join table consists of two columns, each of which is a foreign key column to another table. Each row in a join table represents a relationship between one row in each of the two tables being joined. This technique enables many-to-many relationships.

Consider some example data in our Events and Tags tables.

Sample Events Data

Sample Categories Data

Sample Tags Data

A join table for these two tables would be called EventTags, and would have two columns, EventsId and TagsId. Each of these columns are foreign key columns into their respective tables.

If we want to relate the ios tag to the WWDC event, we create a new row in EventTags:

Join table with a single relationship

We can do this again and again to generate more relationships. Let’s revisit the many-to-many diagram from earlier in the chapter.

The join table representing these relationships looks like this:

Notice that join table doesn’t have an explicit primary key column. Values in both EventsId and TagsId may be duplicated in each column. Indeed, this is the exact property of a join table that allows many-to-many relationships. A join table makes use of a new type of primary key, called a composite primary key. A composite primary key is a combination of columns that is unique and functions as a primary key. So, for example, the primary key of the first row of the example just above is the pair (13, 4). This combination is unique, because the objects with the two IDs can be related to each other in only one way.

In order to enable many-to-many relationships with EF, we need a class to model a join table.

Many to Many Relationship

To model a join table for Event and Tag classes, we will provide a collection navigation property on both sides of the relationship.

Within the Event.cs class we add the following collection property of type Tags:

public ICollection<Tag>? Tags { get; set; }

1

Tags = new List<Tag>();

Within the Tag.cs class we add a collection property of type Events to the other side of the relationship:

public ICollection<Event>? Events { get; set; }

Events = new List<Event>();

Join Entity Type Configuration

Since our join table will make use of a composite primary key, we need to add some additional configuration to EventDbContext.

1
2
3
4
5
6
7

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Event>()
         .HasMany(e => e.Tags)
         .WithMany(e => e.Events)
         .UsingEntity(j => j.ToTable("EventTags"));
}

The method OnModelCreating can be overridden from the base class, DbContext, in order to provide additional configuration for the data store. In this case, we add code that configures EventTag to have a composite primary key consisting of the properties/columns EventId and TagId.

Before proceeding, create and apply a database migration. Be sure to use a unique and descriptive migration name. After running the migration, verify that there is a new join table, EventTags.

Adding a Tag to an Event

Now that we have established a many-to-many relationship between Event and Tag, we can write controller and view code to allow users to add tags to events.

For a user to be able to add a tag to an event, they will need a view in which to do so. Our approach will be to create a view at the path /Tag/AddEvent/X, where X is a path parameter containing the ID of the event we want to add a tag to.

This new view will contain a form with a dropdown that will allow the user to select the tag to add to the event with ID X. To model this form data, we need a new ViewModel.

ViewModels/AddEventTagViewModel

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

using System;
using CodingEvents.Models;
using Microsoft.AspNetCore.Mvc.Rendering;

namespace CodingEvents.ViewModels
{
    public class AddEventTagViewModel
    {

        public int EventId { get; set; }
        public Event? Event { get; set; }

        public List<SelectListItem>? Tags { get; set; }

        public int TagId { get; set; }

        public AddEventTagViewModel(Event theEvent, List<Tag> possibleTags)
        {
            Tags = new List<SelectListItem>();

            foreach (var tag in possibleTags)
            {
                Tags.Add(new SelectListItem
                {
                    Value = tag.Id.ToString(),
                    Text = tag.Name
                });
            }

            Event = theEvent;
        }

        public AddEventTagViewModel()
        {
        }
    }

}

This class models the data that is needed to render and process our form. In order to add a tag to an event, our POST handler will need to know the IDs of the two objects in question. Therefore, our ViewModel has required EventId and TagId properties. It also contains an Event property, which we will use to display details (such as the event name) in the view.

Finally, the ViewModel has a property List<SelectListItem> Tags. As with previous forms containing a dropdown, this property will be used to populate the select element containing the all of the tag options.

The constructor requires an Event object as well as a List<Tag> object. The list will contain a collection of all tags pulled from the database when we call the constructor from within our controller.

Now, let’s create the view template.

Views/Tag/AddEvent.cshtml

Our template needs a form with two inputs. The more obvious input will be the select element containing tag options. Since we also need to submit the event ID in our request, we’ll add a hidden input that holds the value of EventId from our ViewModel.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

@model CodingEvents.ViewModels.AddEventTagViewModel

<h1>Add Tag to Event: @Model.Event.Name</h1>

<form asp-controller="Tag" asp-action="AddEvent" method="post">
   <input type="hidden" value="@Model.Event.Id" name="EventId" />
   <div class="form-group">
      <label asp-for="TagId">Tag</label>
      <select asp-for="TagId" asp-items="Model.Tags"></select>
      <span asp-validation-for="TagId"></span>
   </div>
   <input type="submit" value="Add Tag" />
</form>

GET and POST Handlers

We’re now ready to add handler methods to TagController.

The GET method is simple since it just displays the form.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

// responds to URLs like /Tag/AddEvent/5 (where 5 is an event ID)
public IActionResult AddEvent(int id)
   {
      Event theEvent = context.Events.Find(id);
      List<Tag> possibleTags = context.Tags.ToList();

      AddEventTagViewModel viewModel = new AddEventTagViewModel(theEvent, possibleTags);
      
      return View(viewModel);
   }

This method creates an AddEventTagViewModel using the event specified by the id parameter and the list of all tags from the database. Then it renders the view.

The POST method is more complicated.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

[HttpPost]
   public IActionResult AddEvent(AddEventTagViewModel viewModel)
   {
      if (ModelState.IsValid)
      {
            int eventId = viewModel.EventId;
            int tagId = viewModel.TagId;

            Event theEvent = context.Events.Include(e => e.Tags).Where(e => e.Id == eventId).First();
            Tag theTag = context.Tags.Where(t => t.Id == tagId).First();

            theEvent.Tags.Add(theTag);

            context.SaveChanges();

            return Redirect("/Events/Detail/" + eventId);
      }

      return View(viewModel);
   }

This action method takes in a AddEventTagViewModel object which will be created via model binding. Assuming validation passes (that is, both EventId and TagId are not null) we create a new EventTag object and save it to the database. Then we redirect to the detail view for the given event.

With this code, we can now add a tag to an event. Start up the application and test it out. In order to verify that everything worked, you’ll need to look at the EventTag table in the database to verify a new row is created upon form submission.

In the next section, we’ll work to display tags in the view.

Displaying Tags in the Detail View

Now that we have EventTags data in the database, let’s display the tags for a given event in the view.

We want an event’s tags to be displayed on its detail view, so let’s start in EventsController. The Detail method needs to pass in tag data for the given event. To do this, we must use another .Include() statement with a lambda expression for Tags.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

public IActionResult Detail(int id)
   {
   Event theEvent = context.Events
      .Include(e => e.Category)
      .Include(e => e.Tags)
      .Single(e => e.Id == id);

   EventDetailViewModel viewModel = new EventDetailViewModel(theEvent);
   return View(viewModel);
   }

Now let’s move into EventDetailViewModel. Here, we add data related to an event’s tags to pass into the view.

First, add a new string property named TagText. We will also create a List of Tags called evtTags.

1

public string TagText { get; set; }

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

public EventDetailViewModel(Event theEvent)
{
   EventId = theEvent.Id;
   Name = theEvent.Name;
   Description = theEvent.Description;
   ContactEmail = theEvent.ContactEmail;
   CategoryName = theEvent.Category.Name;

   TagText = "";
   List<Tag> evtTags = theEvent.Tags.ToList();
   for (var i = 0; i < evtTags.Count; i++)
   {
      TagText += ("#" + evtTags[i].Name);
      if (i < evtTags.Count - 1)
      {
         TagText += ", ";
      }
   }
}

We build up the contents of TagText by looping over evtTags and appending tag names, separated by commas. For example, if an event has tags with names "java", "csharp", and "object-oriented", then the TagList will be "#java, #csharp, #object-oriented".

Displaying this data in the view is straightforward. In Views/Events/Detail.cshtml, add an additional row to the table.

1
2
3
4

<tr>
   <th>Tags</th>
   <td>@Model.TagText</td>
</tr>

Now, any tags associated with the given event will display nicely.

To make it easy for users to add a tag to an event, add the following link below the table.

1

<a asp-controller="Tag" asp-action="AddEvent" asp-route-id="@Model.EventId">Add Tag</a>

This creates a URL of the form /Tag/AddEvent/X, where X is the ID of the given event.

Display Items With a Given Tag

In addition to seeing which tags are on an event, we would also like to see all events with a specific tag.

We start by creating a Detail action in Controllers/TagController.cs. This action method should retrieve all Tags objects with a given TagId.

1
2
3
4
5
6

public IActionResult Detail(int id) 
{
   Tag theTag = context.Tags.Include(e => e.Events).Where(t => t.Id == id).First();

   return View(theTag);
}

This controller is accessible at the route /Tag/Detail/X where X is the ID of a specific tag.

The view is similar to other listings that we have created.

Views/Tag/Detail.cshtml

Create the Detail.cshtml file and add the below code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

@model CodingEvents.Models.Tag

@if (Model.Events.ToList().Count == 0)
{
   <h1>No elements with the given tag</h1>
}
else
{
   <h1>Events Tagged: @Model.Name</h1>

   <ul>
      @foreach (var evtTag in Model.Events)
      {
         <li>@evtTag.Name</li>
      }
   </ul>

}

Finally, we add links to the name of each tag in our tag index.

Views/Tag/Index.cshtml:

1
2
3
4

<tr>
      <td>@tag.Id</td>
      <td><a asp-controller="Tag" asp-action="Detail" asp-route-id="@tag.Id">@tag.Name</a></td>
</tr>

Start the app up and test user behavior. Viewing the main tag listing should allow you to click on each tag name and view the events that have that tag.

Check Your Understanding