ASP.NET MVC5实现双表关联搜索功能 关联逻辑未生效问题排查

• 已定义的关联查询变量Product未被实际使用，接口返回的数据源依然是直接查询db.Claims单表，关联逻辑未生效
• 如果需要同时返回两张表的字段，原代码中select返回的Claim实体类没有对应附件字段存储，需要额外定义视图模型承载跨表数据

分两种常见场景调整：

场景1：仅需要过滤出存在对应附件的Claim数据，无需返回附件字段 #

代码调整如下：

public ActionResult Index(int? search)
{
    if (search != null)
    {
        var result = (from P in db.Claims
                     join C in db.ClaimAttachments on P.ClaimID equals C.ClaimID
                     where P.ClaimID == search
                     select new Claim
                     {
                         ClaimID = P.ClaimID,
                         ClaimBatchID = P.ClaimBatchID,
                         PatientControlNumber = P.PatientControlNumber,
                         PatientFirstName = P.PatientFirstName,
                         PatientLastName = P.PatientLastName,
                         ServiceFromDate = P.ServiceFromDate,
                         ServiceToDate = P.ServiceToDate,
                     }).Distinct().ToList(); // 去重避免单条Claim对应多个附件时返回重复数据
        return View(result);
    }
    else
    {
        // 无搜索条件时如果也需要走关联过滤逻辑就用下方关联查询，否则保留原单表查询即可
        var allResult = (from P in db.Claims
                        join C in db.ClaimAttachments on P.ClaimID equals C.ClaimID
                        select new Claim
                        {
                            ClaimID = P.ClaimID,
                            ClaimBatchID = P.ClaimBatchID,
                            PatientControlNumber = P.PatientControlNumber,
                            PatientFirstName = P.PatientFirstName,
                            PatientLastName = P.PatientLastName,
                            ServiceFromDate = P.ServiceFromDate,
                            ServiceToDate = P.ServiceToDate,
                        }).Distinct().ToList();
        return View(allResult);
        // 不需要关联全量可替换为：return View(db.Claims.ToList());
    }
}

场景2：需要同时返回Claim和ClaimAttachments两张表的字段 #

首先新增自定义视图模型承载跨表数据：

public class ClaimWithAttachmentViewModel
{
    // Claim表字段按需添加
    public int ClaimID { get; set; }
    public int ClaimBatchID { get; set; }
    public string PatientControlNumber { get; set; }
    public string PatientFirstName { get; set; }
    public string PatientLastName { get; set; }
    public DateTime ServiceFromDate { get; set; }
    public DateTime ServiceToDate { get; set; }
    // ClaimAttachments表字段按需添加
    public int AttachmentID { get; set; }
    public string FileName { get; set; }
    public string FilePath { get; set; }
}

控制器代码调整如下：

public ActionResult Index(int? search)
{
    // 先定义关联查询基础逻辑
    var query = from P in db.Claims
                join C in db.ClaimAttachments on P.ClaimID equals C.ClaimID
                select new ClaimWithAttachmentViewModel
                {
                    ClaimID = P.ClaimID,
                    ClaimBatchID = P.ClaimBatchID,
                    PatientControlNumber = P.PatientControlNumber,
                    PatientFirstName = P.PatientFirstName,
                    PatientLastName = P.PatientLastName,
                    ServiceFromDate = P.ServiceFromDate,
                    ServiceToDate = P.ServiceToDate,
                    AttachmentID = C.AttachmentID,
                    FileName = C.FileName,
                    FilePath = C.FilePath
                };
    // 追加搜索条件
    if (search != null)
    {
        query = query.Where(x => x.ClaimID == search);
    }
    return View(query.ToList());
}

• 如果使用了自定义视图模型，需要同步修改视图顶部的模型绑定声明为@model IEnumerable<ClaimWithAttachmentViewModel>
• 如果需要实现左连接（无对应附件的Claim数据也需要返回），可将关联逻辑修改为左连接写法

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