How to join database tables in my website?

Question

Here is my code:

albums.cshtml

@page
@using Project.Models
@model Project.Pages.AlbumsModel
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<div class="row">
    <h1 class="display-2">Albums</h1>
    <table class="table">
        <thead class="thead-inverse">
            <tr>
                <th>Album ID</th>
                <th>Artist ID</th>
                <th>Artist name</th>
                <th>Album title</th>
                <th></th>
                <th></th>
            </tr>
        </thead>

        <tbody>
            @foreach (Album album in Model.Albums)
            {
                <tr>
                    <td>@album.AlbumId</td>
                    <td>@album.ArtistId</td>
                    <td>@Html.DisplayFor(modelArtists => album.Artist.Name)</td>
                    <td>@album.Title</td>
                    <td><a asp-page="/Albums/Details" asp-route-id="@album.AlbumId">Details</a></td>
                    <td><a asp-page="/Albums/Edit" asp-route-id="@album.AlbumId">Edit</a></td>
                    <td><a asp-page="/Albums/Delete" asp-route-id="@album.AlbumId">Delete</a></td>   
                    <td><a asp-page="/Albums/AlbumTracks" asp-route-id="@album.AlbumId">Tracks</a></td>       
                </tr>
            }
        </tbody>
    </table>
</div>
<div class="row">
    <p>Enter a title & ArtistID for a new album:&nbsp;</p>
    <form method="POST">
        <div><input asp-for="Album.Title" /></div>
        <div><input asp-for="Album.ArtistId" /></div>
        <input type="submit" />
    </form>
</div>
<div>
    <a asp-page="/Index">Home</a>
</div>

AlbumTracks.cshtml

@page
@using Project.Models
@model Project.Pages.AlbumTracksModel
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<div class="row">
    <h1 class="display-2">Tracks</h1>
    <table class="table">
        <thead class="thead-inverse">
            <tr>
                <th>Track name</th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
            </tr>
        </thead>

        <tbody>
            @foreach (Track track in Model.Tracks)
            {
                <tr>
                    <td>@track.TrackId</td>
                    <td>@track.Name</td>
                </tr>
            }
        </tbody>
    </table>
</div>
@* <div class="row">
    <p>Enter a name & TrackID for a new track:&nbsp;</p>
    <form method="POST">
        <div><input asp-for="Track.Name" /></div>
        <div><input asp-for="Track.TrackId" /></div>
        <input type="submit" />
    </form>
</div> *@
<div>
    <a asp-page="/Index">Home</a>
</div>

AlbumTracks.cshtml.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Project.Models;
using System;
using System.Collections.Generic;
using System.Linq;

namespace Project.Pages
{
    public class AlbumTracksModel : PageModel
    {
        private Chinook db;

        public AlbumTracksModel(Chinook injectedContext)
        {
            db = injectedContext;
        }
        public IEnumerable<Track> Tracks { get; set; }
        public void OnGetAsync()
        {
            ViewData["Title"] = "Chinook Web Site - Tracks";
            Tracks = db.Tracks.Include(a => a.Album);
        }
    }
}

At the moment I'm displaying all tracks from every album in AlbumTracks.cshtml, instead of just the one selected by the user on album.cshtml. I've read this repeatedly, but I'm struggling to work out the syntax for AlbumTracks.cshtml, and maybe I need something more in AlbumTracks.cshtml.cs?

I think maybe I need to use something like this to join the tables:

        if (item.AlbumID == Model.AlbumID)

Any pointers please? TIA.

Edit: GitHub repo

Answer 1

At the moment I'm displaying all tracks from every album in AlbumTracks.cshtml, instead of just the one selected by the user on album.cshtml.

Are you sure what you describe is correct? You display all the tracks from all the albums by using the following code:

Tracks = db.Tracks.Include(a => a.Album);

I think what you want is that you can display the selected album's tracks when click the Tracks link in albums.cshtml:

<td><a asp-page="/Albums/Album Tracks" asp-route-id="@album.AlbumId">Tracks</a></td>

If what I said is correct, you need firstly add id parameter to your /Albums/AlbumTracks method and modify the linq like below:

public class AlbumTracksModel : PageModel
{
    private Chinook db;

    public AlbumTracksModel(Chinook injectedContext)
    {
        db = injectedContext;
    }
    public IEnumerable<Track> Tracks { get; set; }

    public void OnGetAsync(int id)
    {
        ViewData["Title"] = "Chinook Web Site - Tracks";
        Tracks = db.Tracks.Include(a => a.Album).Where(a=>a.AlbumId==id);
    }
}

AlbumTracks.cshtml:

@page
@using Project.Models
@model Project.Pages.AlbumTracksModel
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<div class="row">
    <h1 class="display-2">Tracks</h1>
    <table class="table">
        <thead class="thead-inverse">
            <tr>
                <th>Track name</th>
                <th></th>
                <th></th>
                <th></th>
                <th></th>
            </tr>
        </thead>

        <tbody>
            @foreach (Track track in Model.Tracks)
            {
                <tr>
                    <td>@track.TrackId</td>
                    <td>@track.Name</td>
                </tr>
            }
        </tbody>
    </table>
</div>

Result: