Sillystringz Factory

Setting up a Many-to-Many Relationship in .Net 6 MVC

Github repo: Sillystringz Factory
Readme includes instructions for setting up database using MySQL Workbench

Refer Week11.Solutions for practice projects in preparation for Code Review

Project Overview

Language: C#
Framework: ASP.NET MVC
Database: MySQL (Entity Framework Core)

Develop an app to manage machines and engineers licensed to repair them, demonstrating a many-to-many relationship. User should be able to view all machines and all engineers of the factory, create, edit and delete. When viewing either the details of a machine or engineer, a list should show the engineers licensed to repair a machine or the machines an engineer is licensed to repair.

Problem Statements

  1. How to use a join entity for displaying a core entity's details

  2. How to add and remove join entities from the same page

  3. How not to show supplemental forms when no entries are available

Introduction

Resources

Why use Html Helpers: StackOverflow
Read on MVC and Html Helpers: TutorialsTeacher

Skip to Solutions if introduction to C# or MVC is not required.

Assembly: Project (or app) folder. Contains setup for an app, but may not be root directory as a project may have multiple assemblies or consist of multiple smaller projects.

Every assembly has a Program.cs and AssemblyName.csproj in the app's root, alongside the folders separating controllers, models and views. If using a database, it will also have an appsettings.json.

Sample Code: Factory.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">
    <PropertyGroup>
        <TargetFramework>net6.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
    </PropertyGroup>
    <ItemGroup>
      <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.9" />
      <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.9">
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
        <PrivateAssets>all</PrivateAssets>
      </PackageReference>
      <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="7.0.0" />
    </ItemGroup>
</Project>

Sample Code: Program.cs

global using System;
global using System.Collections.Generic;
global using System.Linq;
global using Microsoft.EntityFrameworkCore;
global using Microsoft.AspNetCore.Builder;
global using Microsoft.AspNetCore.Mvc;
global using Factory.Models;

using Microsoft.Extensions.DependencyInjection;

namespace Factory;

public class Program
{
    public static void Main(string[] args)
    {
        WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
        builder.Services.AddControllersWithViews();
        builder.Services.AddDbContext<FactoryContext>(
            dbContextOptions => dbContextOptions.UseMySql(
                builder.Configuration["ConnectionStrings:DefaultConnection"],
                ServerVersion.AutoDetect(builder.Configuration["ConnectionStrings:DefaultConnection"])
            )
        );
        WebApplication app = builder.Build();

        // app.UseDeveloperExceptionPage();
        app.UseHttpsRedirection();
        app.UseStaticFiles();
        app.UseRouting();

        app.MapControllerRoute(
            name: "default",
            pattern: "{controller=Home}/{action=Index}/{id?}"
        );
        app.Run();
    }
}

Many directives are made global as they are used throughout the rest of the assembly. Based on recollection, there are only one or two other files that require .Builder, so that directive should maybe be just a local directive.

Uncomment out app.UseDeveloperExceptionPage() for viewing exceptions in the browser window, when the app is run, using either dotnet run or dotnet watch run.

Sample Code: appsettings.json

{
    "ConnectionStrings": {
        "DefaultConnection": "Server=[hostname];Port=[portnumber];database=[dbname];uid=[username];pwd=[password];"
    }
}

The appsettings.json file can have multiple connections listed, but will only use one at a time. The connections are stated in the following line in Program.cs:

builder.Services.AddDbContext<FactoryContext>(
    dbContextOptions => dbContextOptions.UseMySql(
        builder.Configuration["ConnectionStrings:DefaultConnection"],
        ServerVersion.AutoDetect(builder.Configuration["ConnectionStrings:DefaultConnection"])
    )
);

A project's .gitignore and README.md go in the project root, apart from the assemblies, even if there is only one.

Database File

Sample Code: FactoryContext.cs

namespace Factory.Models;
public class FactoryContext : DbContext
{
    public DbSet<Engineer> Engineers { get; set; }
    public DbSet<Machine> Machines { get; set; }
    public DbSet<EngineerMachine> EngineerMachines { get; set; }

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

Each property declared corresponds to a table in the database and is responsible for managing its operations: create, pull, update and delete. Pull = querying data to be read.

Models

Folder in assembly: ends with .cs

  • Engineer

  • Machine

  • EngineerMachine

Sample Model:

namespace Factory.Models;
public class Engineer
{
    public int EngineerId { get; set; }
    public string Name { get; set; }
}

Models correspond to entities in a database (table). When data is pulled from the database, it is held by a model, which is used for creating, updating, and reading data. For deletion, only the id is necessary.

Controllers

  • Home: Index

  • Engineers: Create (GET/POST), Details, AddMachine, RemoveMachine, Edit (GET/POST), Delete

  • Machines: Create (GET/POST), Details, AddEngineer, RemoveEngineer, Edit (GET/POST), Delete

Sample Controller:

using Factory.Models;
namespace Factory.Controllers;
public class HomeController : Controllers
{
    private readonly FactoryContext _db;
    public HomeController(FactoryContext db)
    {
        _db = db;
    }

    [Http("/")]
    public ActionResult Index()
    {
        Engineer[] model = new Engineer[]();
        return View(model)
    }
}

Controllers contain methods, the names of which correspond to views. When a route is called for in the browser, the route will resemble /ControllerName/MethodName/*. The route decorator, [Http("/")], is not necessary, but shows the route as it would appear in the browser's url bar.

Controllers pass models, or data held in structures like dictionaries and lists, into the view, which then displays the data. However, the model passed acts as an ui variable that can also pass information back to another method.

Views

Shared/_Layout.cshtml

<!DOCTYPE html>
<html>
<head>
    <title>Sillystringz Factory</title>
    <link rel="stylesheet" href="~/styles.css">
</head>
<body>
    @RenderBody()
</body>
</html>

A view consists of html embedded with C# code. These are, to an extent, what razor pages are. Elements can consist of html helpers (@Html.ActionLink) or tag helpers (asp-for="") on traditional elements to enable use of data and variables.

@{
    Layout = "_Layout";
}
@using Factory.Models;
<!-- @model Engineer -->

<main>
    <ul>
        @foreach (Engineer engr in Model)
        {
            <li>engr.Name</li>
        }
    </ul>
</main>

Adding @model specifies an object or data type that will be generated or used for transferring data from forms. An object of this type can be passed in from the controller to populate the page. For managing multiple models, either use a dictionary or a tuple (no need for @model), or a viewmodel.

Plain Example:
<a href="/Engineers/Details/@engr.EngineerId">@engr.Name</a>

Example of Html Helper:
@Html.ActionLink(@engr.Name, "Details", "Engineers", new { id = @engr.EngineerId })

If routing to a method within the same controller as the current page, the controller does not need to be specified.

Solutions

How to use a join entity for displaying a core entity's details

Model: Engineer.cs

...
public class Engineer
{
    public int EngineerId { get; set; }
    public string Name { get; set; }
    public List<EngineerMachine> Machines { get; }
}

While Engineer holds data for an entry of the Engineers table in the database, the additional List is for retaining the joined entities.

Model: EngineerMachine.cs

...
public class EngineerMachine
{
    public int EngineerMachineId { get; set; }

    public int EngineerId { get; set; }
    public Engineer Engineer { get; set; }

    public int MachineId { get; set; }
    public Machine Machine { get; set; }
}

Like in a join table, the class holds three ids: its primary key, then the key for an engineer and for a machine. The Engineer and Machine objects will hold the data for the joined entities.

Sample Code: View details of an entity (controller method)

public ActionResult Details(int id)
{
    Engineer model = _db.Engineers
        .Include(engineer => engineer.Machines)
        .ThenInclude(machines => machines.Machine)
        .FirstOrDefault(engineer => engineer.EngineerId == id);
    return View(model);
}

Access database with _db object declared in controller. Specify the name of the table. The include statements first load the join entities into List<EngineerMachine> Machines, and then with each join, the data of the related entity is stored in each EngineerMachine in that list.

Modified Code: Setup join entity to hold entity details (controller method)

using Microsoft.AspNetCore.Mvc.Rendering;
...
public ActionResult Details(int id)
{
    EngineerMachine model = new EngineerMachine();
    model.EngineerId = id;
    model.Engineer = _db.Engineers
        .Include(engineer => engineer.Machines)
        .ThenInclude(machines => machines.Machine)
        .FirstOrDefault(engineer => engineer.EngineerId == id);
    ViewBag.MachineId = new SelectList(_db.Machines, "MachineId", "Name");
    return View(model);
}

Directive for .Rendering is for the SelectList, which can be used to populate a dropdownlist in the view: @Html.DropDownList("EngineerId"). It is stored in the ViewBag, a temporary data structure that only lasts from controller to view. The ViewBag is an available dictionary to store additional data that is not part of the model in use. It is separated because the data it provides is only needed to set the id of an engineer selected from the list, which will be passed out from a form in model.MachineId.

SelectList(args): Table to call from, Property to use for value, Property to use for label
Property must be from corresponding model, in this case, Machine.

View: Details/Engineer/?id

@using Factory.Models.EngineerMachine
<!-- Or
@using Factory.Models;
@model EngineerMachine
-->
...
@using (Html.BeginForm("AddMachine", "Engineers", FormMethod.Post))
{
    @Html.HiddenFor(model => model.EngineerId)
    @Html.Label("Select engineer")
    @Html.DropDownList("MachineId")
    <input type="submit" value="Add Machine" />
}
@if (@Model.Engineer.Machines.Count == 0)
{
    <p>This engineer has not been assigned to any machines yet.</p>
}
else
{
    <ul>
        @foreach (EngineerMachine machine in Model.Engineer.Machines)
        {
            ....
        }
    </ul>
}

Controller Method: [HttpPost] AddMachine

[HttpPost]
public ActionResult AddMachine(EngineerMachine model)
{
    bool hasRelation = _db.EngineerMachines.Any(join => 
        join.MachineId == model.MachineId
        && join.EngineerId == em.EngineerId);
    if (!hasRelation)
    {
        _db.EngineerMachines.Add(model);
        _db.SaveChanges();
    }
    return RedirectToAction("Details", new { id = model.EngineerId });
}

Method receives instance of join entity with data sent from the view when the form was submitted. First, hasRelation holds the result of determining if the join table already has this join. If not, then the provided instance is added to the database.

How to add and remove join entities from the same page

Sample Code: Delete entity (controller method)

public ActionResult Delete(int id)
{
    Engineer rm = _db.Engineers
        .FirstOrDefault(engineer => engineer.EngineerId == id);
    _db.Engineers.Remove(rm);
    _db.SaveChanges();
    return RedirectToAction("Index", "Home");
}

If Delete is not separated into a confirmation, but is just a button that passes an id to a url, it does not have to be a post method, or specified by [HttpPost].

Adapted Code: Remove a join entity (controller method)

[HttpPost]
public ActionResult RemoveMachine(int joinId)
{
    EngineerMachine rm = _db.EngineerMachines.FirstOrDefault(join => 
        join.EngineerMachineId == joinId;
    int eId = rm.EngineerId;
    _db.EngineerMachines.Remove(rm);
    _db.SaveChanges();
    return RedirectToAction("Details", new { id = eId });
}

Get engineer id because database automatically populates the id, so when the object is deleted, the id is lost. The arg, joinId, is for the join entity being deleted. In order to redirect, the engineer id is needed.

How not to show supplemental forms when no entries are available

Sample Code: Display details of a given entity (controller method)

...
public ActionResult Details(int id) 
{
    EngineerMachine model = new EngineerMachine();
    model.EngineerId = id;
    model.Engineer = _db.Engineers
        .Include(engineer => engineer.Machines)
        .ThenInclude(machines => machines.Machine)
        .FirstOrDefault(engineer => engineer.EngineerId == id);
    return View(model);
}

Modified Code: Add if-statement to provide a select list to populate a dropdown, allowing user to add a relationship with a machine

...
if (_db.Machines.Count() != 0)
    ViewBag.MachineId = new SelectList(_db.Machines, "MachineId", "Name");
return View(model);

Place select list in viewbag as the model, EngineerMachine, does not have a structure for holding the list. Do not add such a property to the join entity's model class, since the join entity only represents and manages one relationship at a time.

Modified Code: Add if-statement to view that if viewbag property is not null, display the dropdownlist

@if (ViewBag.MachineId == null)
{
    <p>There are no machines added yet.</p>
    <p>@Html.ActionLink("Add Machine", "Create", "Machines")</p>
}
else
{
    @using (Html.BeginForm("AddMachine", "Engineers", FormMethod.Post))
    ...
}

Refer Details/Engineer/?id in How to use a join entity for displaying a core entity's details.