Search code examples
c#asp.net-coreentity-framework-corerazor-pagesnullreferenceexception

Join of Two Tables Not Working on Razor Page

I have a report page that I am trying to create on a Razor page app and I cannot get it to work. I have a similar working app and cannot see where I am doing anything differently, but must be missing something obvious.

Here is the view in question:

@page
@model RabiesShotTracker.Pages.Reports.UnpaidBills.IndexModel

<div id="UnpaidBillsIndex" class="container p-3">
    <div class="row pt-4">
        <div class="col-6">
            <h2 class="text-primary">Unpaid Bills</h2>
        </div>
    </div>

    <br /><br />

    <form asp-page="./Index" method="post">
        <div id="UnpaidBillsReport" class="row pt-4">
            <div class="col-25">
                <h2 class="text-primary" align="center">Public Health</h2>
                <h3 class="text-primary" align="center">Rabies Shots Unpaid Bills</h3>
            </div>
                <table id="DataTable" class="table table-bordered" style="width:75%" align="center">
                    <thead>
                        <tr>
                            <th style ="text-align: center">
                                Incident No
                            </th>
                            <th style="text-align: center">
                                Patient Name
                            </th>
                            <th style="text-align: center">
                                Date of Birth
                            </th>
                            <th style="text-align: center">
                                Exposure Date
                            </th>
                            <th style="text-align: center">
                                Date Shot Scheduled
                            </th>
                            <th style="text-align: center">
                                Date Shot Received
                            </th>
                        </tr>
                    </thead>
                    <tbody>

                        @for (var i = 0; i < Model.Client.Shots.Count; i++)
                        {
                            <tr>
                                <td style="width: 15%">
                                    <div class="mb-3">
                                        <input asp-for="Client.Shots[i].IncidentNo" type="text" readonly class="form-control-plaintext" />
                                    </div>
                                </td>
                                <td style="width: 25%">
                                    <div class="mb-3">
                                        <input asp-for="Client.FullName" type="text" readonly class="form-control-plaintext" />
                                    </div>
                                </td>
                                <td style="width: 15%">
                                    <div class="mb-3">
                                        <input asp-for="Client.Birthdate" type="text" readonly class="form-control-plaintext" />
                                    </div>
                                </td>
                                <td style="width: 15%">
                                    <div class="mb-3">
                                        <input asp-for="Client.ExposureDate" type="text" readonly class="form-control-plaintext" />
                                    </div>
                                </td>
                                <td style="width: 15%">
                                    <div class="mb-3">
                                        <input asp-for="Client.Shots[i].ShotSchedDate" type="text" readonly class="form-control-plaintext" />
                                    </div>
                                </td>
                                <td style="width: 15%">
                                    <div class="mb-3">
                                        <input asp-for="Client.Shots[i].ShotRecdDate" type="text" readonly class="form-control-plaintext" />
                                    </div>
                                </td>
                            </tr>
                        }
                    </tbody>
                </table>
    </form>
</div>

And here is the .cs behind it:

using RabiesShotTracker.Data;
using RabiesShotTracker.Model;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;

namespace RabiesShotTracker.Pages.Reports.UnpaidBills
{
    public class IndexModel : PageModel
    {
        private readonly ApplicationDbContext _db;

        public Client Client { get; set; }
        public Shot Shot { get; set; }


        public IndexModel(ApplicationDbContext db)
        {
            _db = db;
        }

        public void OnGet(int Id)
        {
            Client = _db.Client
                    .Include(client => client.Shots).FirstOrDefault(client => client.ClientId == Id);
        }

    }
}

Here is the relevant portion of the Client model:

using System.ComponentModel.DataAnnotations;

namespace RabiesShotTracker.Model
{
    public class Client
    {
        [Key]
        [Display(Name = "Client Id")]
        public int ClientId { get; set; }

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

        [Required]
        [Display(Name = "Client No (###)")]
        public string? ClientNo { get; set; }

        [Required]
        [Display(Name = "Incident No")]
        public string? IncidentNo { get; set; }

And here is the relevant portion of the Shot model:

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

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

        [Required]
        public int ClientId { get; set; }

        public Client? Client { get; set; }

        [Required]
        [Display(Name = "Incident No")]
        public string? IncidentNo { get; set; }
        
        [Required]
        [Display(Name = "Client No")]
        public string? ClientNo { get; set; }

When I run the app, I am getting a NullReferenceException: Object reference not set to an instance of an object. at the line:

@for (var i = 0; i < Model.Client.Shots.Count; i++)

Both tables (Client and Shot) have values in them, so I am not sure what I am doing wrong? Am I missing something blatantly obvious?

Thanks in advance for any help you can provide. I have been staring at this for way too long.

Solution

  • Both tables (Client and Shot) have values in them, so I am not sure what I am doing wrong? Am I missing something blatantly obvious?

    Well, first of all your Shot table design or model defination has violated the 3NF database normaliation because, by Client ID you can access all the entities from shot table therefore, IncidentNo and ClientNo are just repetation of Client table.

    However, your table relationship shouldn't have the issue for getting data by joining them with ClientId. I have investigated along with your code snippet and tried to insert few manual data and it seems working as expected.

    What, I am assuming please, check your search key which is the Id you are passing in your OnGet(int Id) method.

    Apart from that, you should also check your ApplicationDbContext defination and if that has created your database table schema accordingly.

    On top of that, please check your ClientId if that has correct maching on Shot table.

    I have tried the same way you did, and I have executed migration command and it has created database table as following:

    Database Table After Migration:

    enter image description here

    Appsetting.json:

    "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\ProjectModels;Database=RazorPageTableJoinDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

    Db Context Class:

    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

        public DbSet<Client> Clients { get; set; }
        public DbSet<Shot> Shots { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            

        }
    }

    Program.cs Class:

    var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(x => x.UseSqlServer(connectionString));

// Add services to the container.
builder.Services.AddRazorPages();

    Razor Page.cs:

    public class ForeignKeyTableJoinModel : PageModel
    {
        private readonly ApplicationDbContext _db;

        public Client Client { get; set; }
        public Shot Shot { get; set; }


        public ForeignKeyTableJoinModel(ApplicationDbContext db)
        {
            _db = db;
        }

        public void OnGet()
        {
            var Id = 1;
            
            Client = _db.Clients
                    .Include(client => client.Shots).FirstOrDefault(client => client.ClientId == Id);
        }
    }

    Output:

    enter image description here

    enter image description here

    Note: Make sure, you have correct data in regards of your given ClientId, and in database table they have correct relation. Also, you are sending query into correct database in your appsetting.json connection string.