如何通过外键在Index页面引用关联模型的字段值

我在Officer模型中通过外键关联了警员所属的Agency模型，支持用户在警员创建页面为警员分配机构。但在Officers列表页面（Index）中，希望显示该外键对应的Agency模型的AgencyName文本值（可同时显示外键ID），而非仅显示外键ID，以提升用户体验。

模型代码 #

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

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; }
        [ForeignKey("Agency")]
        public int AgencyId { get; set; }
        [Display(Name = "Agency ID")]
        public Agency? Agency { get; set; }
    }
}

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; }
    }
}

Index页面视图代码（显示AgencyName的部分） #

<tbody>
    @foreach(var obj in Model?.Officer)
    {
    <tr>
        <td width="30%">@obj.OfficerDisplayName</td>
        <td width="10%">@obj.AgencyId</td>
        <td width="30%">@obj.Agency?.AgencyName</td>
        <td width="15%">@obj.UpdatedBy</td>
        <td width="15%">@obj.UpdateDate.ToShortDateString()</td>
        <td width="10%">
            <div class="w-75 btn-group" role="group">
                <a asp-page="Edit" asp-route-id="@obj.Id" class="btn btn-primary mx-2">
                    <i class="bi bi-pencil-square"></i>
                </a>
                <a asp-page="Delete" asp-route-id="@obj.Id" class="btn btn-danger mx-2">
                    <i class="bi bi-trash-fill"></i>
                </a>
            </div>
        </td>
    </tr>
    }
</tbody>

Index.cshtml.cs代码 #

using DWITracker.Data;
using DWITracker.Model;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Reflection;

namespace DWITracker.Pages.Officers;

public class IndexModel : PageModel
{
    public IEnumerable<Officer> Officer { get; set; }

    private readonly ILogger<IndexModel> _logger;
    private readonly ApplicationDbContext _db;

    [BindProperty]
    public List<Officer>? OfficersList { get; set; }

    public IndexModel(ILogger<IndexModel> logger, ApplicationDbContext db)
    {
        _logger = logger;
        _db = db;
    }

    public string OfficerSort { get; set; }
    public string CurrentFilter { get; set; }

    public async Task OnGetAsync(string sortOrder, string searchString, List<Officer>OfficersList)
    {

        this.OfficersList = await _db.Officer.Include(x => x.Agency).AsNoTracking().ToListAsync();

        OfficerSort = sortOrder == "Officer_Asc_Sort" ? "Officer_Desc_Sort" : "Officer_Asc_Sort";
        CurrentFilter = searchString;

        IQueryable<Model.Officer> findOfficer = from s in _db.Officer select s;

        if (!String.IsNullOrEmpty(searchString))
           findOfficer = findOfficer.Where(s => s.OfficerDisplayName.Contains(searchString));

        switch (sortOrder)
        {
            case "Officer_Asc_Sort":
                findOfficer = findOfficer.OrderBy(s => s.OfficerDisplayName);
                break;
            case "Officer_Desc_Sort":
                findOfficer = findOfficer.OrderByDescending(s => s.OfficerDisplayName);
                break;
            default:
                findOfficer = findOfficer.OrderBy(s => s.OfficerDisplayName);
                break;
        }
        OfficersList = await findOfficer.AsNoTracking().ToListAsync();
    }
}

1. 修正后台查询逻辑，确保加载Agency导航属性 #

当前后台代码中，虽然一开始通过Include(x => x.Agency)加载了关联数据，但后续重新查询时未包含该关联，导致Agency导航属性为空。同时方法参数中的List<Officer> OfficersList属于冗余参数，需移除。

修改后的OnGetAsync方法：

public async Task OnGetAsync(string sortOrder, string searchString)
{
    OfficerSort = sortOrder == "Officer_Asc_Sort" ? "Officer_Desc_Sort" : "Officer_Asc_Sort";
    CurrentFilter = searchString;

    // 初始化查询时就包含Agency关联
    IQueryable<Model.Officer> findOfficer = _db.Officer.Include(x => x.Agency);

    if (!String.IsNullOrEmpty(searchString))
        findOfficer = findOfficer.Where(s => s.OfficerDisplayName.Contains(searchString));

    switch (sortOrder)
    {
        case "Officer_Asc_Sort":
            findOfficer = findOfficer.OrderBy(s => s.OfficerDisplayName);
            break;
        case "Officer_Desc_Sort":
            findOfficer = findOfficer.OrderByDescending(s => s.OfficerDisplayName);
            break;
        default:
            findOfficer = findOfficer.OrderBy(s => s.OfficerDisplayName);
            break;
    }
    OfficersList = await findOfficer.AsNoTracking().ToListAsync();
}

2. 修正视图中的循环数据源 #

视图中当前循环的是Model?.Officer，但后台赋值的是OfficersList，需统一：

@foreach(var obj in Model?.OfficersList)

3. 优化Agency信息显示（可选） #

若需同时显示AgencyID和名称，可修改对应单元格的内容：

<td width="30%">@obj.AgencyId - @obj.Agency?.AgencyName</td>

或保留原有分开显示的方式，确保@obj.Agency?.AgencyName能正常取值。

内容的提问来源于stack exchange，提问作者MelB