[FIXED] How to Join and Display Data from two models on a single razor page (no mvc)

Issue

I have two models (Child and Shot) that I would like to join so that I can display information on a single razor page.

This small historical view app basically has client demographics in one model (Model Name is Child) and client immunizations in another table (Model Name is Shot). I have an index page that a user will click a 'View Immunization Details' details button next to a client's ID number and client name. This will take them to the page where I would like the joined data to display.

The top half of the page will display the demographics (ID, name, address, date of birth, etc) from the Child model and the lower half of the page will display a table of any immunization info for that particular client (from the Shot model). (immunization date, dose, shot description, etc)

This is a one to many relationship between Child and Shot. One Child record can have many Shot records.

In reviewing how to get two models to display on one page I have only seen where the data did NOT need to be filtered by the results from one model. As stated above, I need to show ONLY the shots that pertain to a SINGLE client on the Details page, NOT all shots from the Shot table.

Here is my code as I have it so far:

First, the Child model:

using System.ComponentModel.DataAnnotations;

namespace HealthyShots.Models
{
    public class Child
    {
        [Key]
        [Display(Name ="Child ID")]
        public int ChildId { get; set; }
        public List<Shot> Shots { get; set; }
        [Display(Name ="Last Name")]
        public string? LastName { get; set; }
        [Display(Name = "First Name")]
        public string? FirstName { get; set; }
        [Display(Name = "MI")]
        public string? MiddleInitial { get; set; }
        [Display(Name = "Full Name")]
        public string FullName
        {
            get
            {
                return LastName + ", " + FirstName + " " + MiddleInitial;
            }
        }
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        [Display(Name = "Date of Birth")]
        public DateTime? BirthDate { get; set; }
        public string? Addr1 { get; set; }
        public string? Addr2 { get; set; }
        public string? City { get; set; }
        public string? State { get; set; }
        public string? Zip { get; set; }
    }
}

Then, the Shot model:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace HealthyShots.Models
{
    public class Shot
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int ShotId { get; set; }

        [Required]
        public int ChildId { get; set; }
        public Child Child { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        [Display(Name = "Immunization Date")]
        public DateTime? Date { get; set; }
        public string? Dose { get; set; }
        [Display(Name = "Shot Number")]
        public int? ShotNo { get; set; }
        [Display(Name ="Shot Description")]
        public string? ShotDesc { get; set; }
    }
}

Then, my .cs (so far) for the Details razor page:

using HealthyShots.Data;
using HealthyShots.Models;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;

namespace HealthyShots.Pages.Details
{
    public class DetailsModel : PageModel
    {
        private readonly ApplicationDbContext _db;

        public Child Child { get; set; }
        public Shot Shot { get; set; }
        

        public DetailsModel(ApplicationDbContext db)
        {
            _db = db;
        }
        
        public void OnGet(int Id)
        {
            _db.Child
                .Include(child => child.Shots)
                .FirstOrDefault(child => child.ChildId == Id);
        }

    }
}

And my razor view:

@page
@model HealthyShots.Pages.Details.DetailsModel

@{
    ViewData["Title"] = "Immunization Details";
}

<h2>Details</h2>

<div>
    <h4>Demographic Information</h4>
    <hr />

        <table class="table table-bordeless" style="width:100%">
            <tr>
                <td style="width: 10%">
                    <div class="mb-3">
                        <label asp-for="Child.ChildId"></label>
                        <input asp-for="Child.ChildId" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 30%">
                    <div class="mb-3">
                        <label asp-for="Child.LastName"></label>
                        <input asp-for="Child.LastName" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 30%">
                    <div class="mb-3">
                        <label asp-for="Child.FirstName"></label>
                        <input asp-for="Child.FirstName" class="form-control"/>
                    </div>
                </td>
                <td style="width: 5%">
                    <div class="mb-3">
                        <label asp-for="Child.MiddleInitial"></label>
                        <input asp-for="Child.MiddleInitial" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.BirthDate"></label>
                        <input asp-for="Child.BirthDate" type="date" disabled class="form-control"/>
                    </div>
                </td>
            </tr>
        </table>
        <table class="table table-bordeless" style="width:100%">
            <tr>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.Addr1"></label>
                        <input asp-for="Child.Addr1" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.Addr2"></label>
                        <input asp-for="Child.Addr2" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 25%">
                    <div class="mb-3">
                        <label asp-for="Child.City"></label>
                        <input asp-for="Child.City" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 5%">
                    <div class="mb-3">
                        <label asp-for="Child.State"></label>
                        <input asp-for="Child.State" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 20%">
                    <div class="mb-3">
                        <label asp-for="Child.Zip"></label>
                        <input asp-for="Child.Zip" disabled class="form-control"/>
                    </div>
                </td>
            </tr>
        </table>
<br />
<br />


    <h4>Immunizations</h4>
    <hr />

          <table class="table table-bordeless" style="width:100%">
            <tr>
                <td style="width: 20%">
                    <div class="mb-3">
                        <label asp-for="Shot.Date"></label>
                        <input asp-for="Shot.Date" type="date" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 20%">
                    <div class="mb-3">
                        <label asp-for="Shot.Dose"></label>
                        <input asp-for="Shot.Dose" disabled class="form-control"/>
                    </div>
                </td>
                <td style="width: 60%">
                    <div class="mb-3">
                        <label asp-for="Shot.ShotDesc"></label>
                        <input asp-for="Shot.ShotDesc" disabled class="form-control"/>
                    </div>
                </td>
            </tr>
            }
        </table>

    <a asp-page="/Children/Index">Back to List</a>
</div>

I know I am missing code. I have googled and searched here for how the foreign key works, but still don't fully understand it. I realize that I have to tell Visual Studio that the Id (in the Child model) must be the same as the ChildId (in the Shot model), but I am not sure WHERE to do that or WHAT the correct syntax is.

I also need a wee bit of guidance on how to reference these items on the razor page. Once they are joined via foreign key, can I just access them with asp tag helpers like I would if it were a single model being referenced? Like I have done in my code so far?

Thanks in advance for any guidance you can provide. I am a beginner, so please understand that when you frame your answer.

Solution

Create relationships using conventions

In EF Core one way to define entity relationships is using conventions. This means you have to add specific properties with specific names to your entities so that EF understands the relationship between them.

Example:

public class Child
{
    public int ChildId { get; set; }

    public List<Shot> Shots { get; set; }
}

public class Shot
{
    public int ShotId { get; set; }

    public int ChildId { get; set; }
    public Child Child { get; set; }
}

In the above scenario EF will automatically create a 1 to many relationship between Shot and Child. One shot is linked to one child but one child can be linked to multiple shots. Shot.ChildId will be a foreign key to Child.ChildId.

https://www.learnentityframeworkcore.com/conventions/one-to-many-relationship

Loading related data from database

Now if you want to retrieve a specific child from database with all the related shots you can do:

var child = _db.Child
    .Include(child => child.Shots)
    .FirstOrDefault(child => child.ChildId == Id);

Include method tells EF to populate Child.Shots with all the shots that are related to this kid.

Similarly if you when you load the shots you can do:

var shots = _db.Shot
    .Include(shot => shot.Child)
    .ToList();

https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager

Configure relationships manually

You can also configure relationships manually:

https://www.learnentityframeworkcore.com/configuration/one-to-many-relationship-configuration

Answered By - Dimitris Maragkos

Answer Checked By - Robin (PHPFixing Admin)