级联下拉列表实现遇阻：问题排查与单表方案咨询

一、现有级联下拉功能故障排查 #

1. Razor Pages方法命名规范错误

Razor Pages后台方法必须遵循OnGetXXX/OnPostXXX命名规则，原方法缺少前缀导致AJAX请求无法匹配端点，修改PageModel方法：

public JsonResult OnGetGetAgencies()
{
    var agencies = _db.Agency.OrderBy(x => x.AgencyName).ToList();
    return new JsonResult(agencies);
}

public JsonResult OnGetGetOfficers(int id)
{
    var officers = _db.Officer.Where(x => x.AgencyId == id)
                              .OrderBy(x => x.OfficerDisplayName)
                              .ToList();
    return new JsonResult(officers);
}

2. AJAX请求URL错误

Razor Pages路由需指向页面路径+handler参数，修改JS中的请求地址：

// LoadAgencies中的URL
url: '/Incidents/Create?handler=GetAgencies',

// LoadOfficers中的URL
url: '/Incidents/Create?handler=GetOfficers&id=' + agencyId,

3. 缺少下拉列表change事件监听

未绑定#agency的选中事件，导致切换部门时不触发警员加载，在$(document).ready中添加：

$('#agency').on('change', function() {
    var selectedAgencyId = $(this).val();
    if (selectedAgencyId && selectedAgencyId !== "---Select Arresting Agency---") {
        LoadOfficers(selectedAgencyId);
    } else {
        $('#officer').empty().append('<option>---Select Arresting Officer---</option>').attr('disabled', true);
    }
});

4. JS语法错误

LoadAgencies函数后多余闭合}，删除该冗余符号，确保代码语法正确。

5. 选项值引号缺失

拼接option时value属性未加引号，可能导致特殊字符出错，修改为：

$('#agency').append('<option value="' + data.id + '">' + data.AgencyName + '</option>');
// LoadOfficers同理
$('#officer').append('<option value="' + data.id + '">' + data.OfficerDisplayName + '</option>');

6. Officer模型外键配置缺失

EF Core无法自动识别导航属性关联，修改Officer模型添加显式外键：

public class Officer
{
    [Key]
    public int Id { get; set; }
    // 其他字段保留
    public int AgencyId { get; set; }
    [ForeignKey(nameof(AgencyId))]
    public Agency Agency { get; set; }
}

二、单表架构（合并Agency到Officer）修改建议 #

1. 清理模型

删除Agency实体类，修改Officer模型移除导航属性：

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; }
    [Display(Name = "Agency")]
    public string? OfficerAgency { get; set; }
    [Display(Name = "Added/Updated By")]
    public string UpdatedBy { get; set; }
    [Display(Name = "Date Added/Updated")]
    [DataType(DataType.Date)]
    public DateTime UpdateDate { get; set; }
}

2. 修改PageModel的AJAX方法

• 获取去重后的机构列表：

public JsonResult OnGetGetAgencies()
{
    var agencies = _db.Officer
                      .Where(x => !string.IsNullOrEmpty(x.OfficerAgency))
                      .Select(x => x.OfficerAgency)
                      .Distinct()
                      .OrderBy(x => x)
                      .ToList();
    var agencyOptions = agencies.Select(a => new { id = a, AgencyName = a }).ToList();
    return new JsonResult(agencyOptions);
}

• 根据机构名称筛选警员：

public JsonResult OnGetGetOfficers(string agencyName)
{
    var officers = _db.Officer.Where(x => x.OfficerAgency == agencyName)
                              .OrderBy(x => x.OfficerDisplayName)
                              .ToList();
    return new JsonResult(officers);
}

3. 修改前端JS

• 调整请求参数和URL：

function LoadOfficers(agencyName) {
    $('#officer').empty();
    $.ajax({
        url: '/Incidents/Create?handler=GetOfficers&agencyName=' + encodeURIComponent(agencyName),
        success: function (response) {
            if (response != null && response != undefined && response.length > 0) {
                $('#officer').attr('disabled', false);
                $('#officer').append('<option>---Select Arresting Officer---</option>');
                $.each(response, function (i, data) {
                    $('#officer').append('<option value="' + data.id + '">' + data.OfficerDisplayName + '</option>');
                });
            } else {
                $('#officer').attr('disabled', true);
                $('#officer').append('<option>---Arresting Officers Not Available---</option>');
            }
        },
        error: function (error) {
            alert(error.responseText);
        }
    });
}

• 更新change事件参数传递：

$('#agency').on('change', function() {
    var selectedAgencyName = $(this).val();
    if (selectedAgencyName && selectedAgencyName !== "---Select Arresting Agency---") {
        LoadOfficers(selectedAgencyName);
    } else {
        $('#officer').empty().append('<option>---Select Arresting Officer---</option>').attr('disabled', true);
    }
});

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