How to reference a foreign key on a non-MVC Razor page

Question

I have the following model with a foreign key (public Agency Agency):

using System.ComponentModel.DataAnnotations;

namespace DWITracker.Model
{
    public class Officer
    {
        [Key]
        public int Id { get; set; }
        [Display(Name ="Officer Name (Last, First, MI)")]
        public string? OfficerDisplayName { get; set; }
        [Display(Name = "First Name")]
        public string? OfficerFirstName { get; set; }
        [Display(Name = "MI")]
        public string? OfficerMiddleInitial { get; set; }
        [Display(Name = "Last Name")]
        public string? OfficerLastName { get; set; }
        public Agency Agency { get; set; }
    }
}

When a user is updating this table (from a create view) with a new officer, I need them to be able to select the foreign key (using the Agency dropdown) so they can be assigned to an Agency.

For reference, here is the Agency model class:

using System.ComponentModel.DataAnnotations;

namespace DWITracker.Model
{
    public class Agency
    {
        [Key]
        public int Id { get; set; }
        [Required]
        [Display(Name = "Agency")]
        public string AgencyName { get; set; }
    }
}

I have the foreign key so that I can use cascading dropdowns on another (unrelated to this) view page.

Here is my current .cs for this:

public IEnumerable<Agency> DisplayAgencyData { get; set; }

public async Task OnGet()
{
    await _db.Agency.Select(a => a.Id).ToListAsync();
    DisplayAgencyData = await _db.Agency.ToListAsync();
}

And, here is the relevant portion of my view:

<td style="width: 40%">
    <div class="mb-3">
        <label asp-for="Officer.Agency"></label>
        <select asp-for="Officer.Agency" id="Select3" class="form-select" asp-items="@(new SelectList(Model.DisplayAgencyData.OrderBy(x => x.AgencyName),"Id", "AgencyName"))">
            <option value="" selected disabled>---Select Associated Agency---</option>
        </select>
    </div>
</td>

I have tried many different things, but I keep getting an error:

The Agency field is required.

When I view the Officer table in SQL Server Mgmt Studio, I see that this field is called AgencyId rather than just Agency, but I cannot use that on my razor view without error.

Any assistance would be greatly appreciated. Please respond so a beginner can understand your answer!

Answer 1

In your Officer model, The type of Agency property is Agency. But in your dropdown list, the type of option's value is int, When you select an option and submit the form. A int value will attempt to bind to property of type Agency, Model binding will fail and the value of Agency property will be null, Because Agency is non-nullable, So you will get this error.

The solution is create a foreign key manually and bind the value of option to this foreign key.

[ForeignKey("Agency")]
public int AgencyId { get; set; }
public Agency? Agency { get; set; }

Then change your view code like:

<td style="width: 40%">
    <div class="mb-3">
        <label asp-for="Officer.Agency"></label>
        <select asp-for="Officer.AgencyId " id="Select3" class="form-select" asp-items="@(new SelectList(Model.DisplayAgencyData.OrderBy(x => x.AgencyName),"Id", "AgencyName"))">
            <option value="" selected disabled>---Select Associated Agency---</option>
        </select>
    </div>
</td>

Note: if your dotnet version is .Net 6 or .Net 7, You need to add ? to Agency property to make it nullable. This is because we bind value to AgencyId instead of Agency, So the value of Agency will be null in Model binding, If Agency is non-nullable, the project will also show this error.