使用INNER JOIN关联两表时无法获取Class_Name的问题解决

我正在使用C# Entity Framework进行查询，通过INNER JOIN关联Tbl_Registration与Tbl_Class两张表以获取指定列。其中Tbl_Registration表包含Class_ID列，Tbl_Class表包含Class_Name列。但目前查询无法获取预期的Class_Name，仅返回Class_ID，请问如何修改代码以通过INNER JOIN成功获取Class_Name？

当前代码实现如下：

public ActionResult TestSchedule(int? selectedClassId)
{
    IQueryable<Tbl_Registration> registrations = dbContext.Tbl_Registration;

    if (selectedClassId.HasValue && selectedClassId.Value > 0)
    {
        registrations = registrations.Where(r => r.Class_ID == selectedClassId.Value);
    }

    registrations = dbContext.Tbl_Registration
                             .Where(r => r.Class_ID.HasValue)
                             .Join(dbContext.Tbl_Class,
                                   reg => reg.Class_ID.Value,
                                   cls => cls.Class_ID,
                                   (reg, cls) => new Tbl_Registration
                                                 {
                                                     NameOfApplicant = reg.NameOfApplicant,
                                                     Father_Name = reg.Father_Name,
                                                     Father_Email = reg.Father_Email,
                                                     Father_MobileNumber = reg.Father_MobileNumber,
                                                     DateOfBirth = reg.DateOfBirth,
                                                     Created_On = reg.Created_On,
                                                     Picture = reg.Picture,
                                                     B_Certificate = reg.B_Certificate,
                                                     Class_Name = cls.Class_Name
                                                 });

    ViewBag.ClassList = dbContext.Tbl_Class
                                 .Select(c => new SelectListItem  
                                                  { 
                                                      Value = c.Class_ID.ToString(), 
                                                      Text = c.Class_Name
                                                  })
                                 .ToList();

    return View(registrations.ToList());
}

1. 实体类型不匹配：你在Join的结果中创建了Tbl_Registration实体实例，但该实体类本身并不包含Class_Name属性（这个属性属于Tbl_Class），强行赋值不会被EF识别，最终查询结果自然不会包含Class_Name。
2. 过滤条件丢失：你先基于selectedClassId对registrations做了过滤，但之后又重新从dbContext.Tbl_Registration初始化查询，导致之前的过滤逻辑完全失效。

方案1：使用DTO（数据传输对象） #

创建一个包含所需字段的DTO类，用来承载联合查询的结果，这样可以自由包含来自两张表的字段：

步骤1：定义DTO类 #

public class RegistrationWithClassDto
{
    public string NameOfApplicant { get; set; }
    public string Father_Name { get; set; }
    public string Father_Email { get; set; }
    public string Father_MobileNumber { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public DateTime? Created_On { get; set; }
    public byte[] Picture { get; set; }
    public byte[] B_Certificate { get; set; }
    public string Class_Name { get; set; } // 来自Tbl_Class的字段
}

步骤2：修改查询代码 #

public ActionResult TestSchedule(int? selectedClassId)
{
    // 初始查询，保留过滤条件
    IQueryable<Tbl_Registration> registrations = dbContext.Tbl_Registration
                                                          .Where(r => r.Class_ID.HasValue);

    if (selectedClassId.HasValue && selectedClassId.Value > 0)
    {
        registrations = registrations.Where(r => r.Class_ID == selectedClassId.Value);
    }

    // 关联查询并映射到DTO
    var registrationList = registrations
                             .Join(dbContext.Tbl_Class,
                                   reg => reg.Class_ID.Value,
                                   cls => cls.Class_ID,
                                   (reg, cls) => new RegistrationWithClassDto
                                                 {
                                                     NameOfApplicant = reg.NameOfApplicant,
                                                     Father_Name = reg.Father_Name,
                                                     Father_Email = reg.Father_Email,
                                                     Father_MobileNumber = reg.Father_MobileNumber,
                                                     DateOfBirth = reg.DateOfBirth,
                                                     Created_On = reg.Created_On,
                                                     Picture = reg.Picture,
                                                     B_Certificate = reg.B_Certificate,
                                                     Class_Name = cls.Class_Name
                                                 })
                             .ToList();

    ViewBag.ClassList = dbContext.Tbl_Class
                                 .Select(c => new SelectListItem  
                                                  { 
                                                      Value = c.Class_ID.ToString(), 
                                                      Text = c.Class_Name
                                                  })
                                 .ToList();

    return View(registrationList);
}

方案2：使用EF导航属性（更简洁） #

如果你的实体类已经配置了导航属性（推荐做法），可以直接通过导航属性关联查询，无需手动写Join：

步骤1：确保实体类配置了导航属性 #

public class Tbl_Registration
{
    // 原有属性...
    public int? Class_ID { get; set; }

    // 导航属性
    public virtual Tbl_Class Tbl_Class { get; set; }
}

public class Tbl_Class
{
    public int Class_ID { get; set; }
    public string Class_Name { get; set; }

    // 可选：反向导航属性
    public virtual ICollection<Tbl_Registration> Tbl_Registrations { get; set; }
}

步骤2：简化查询代码 #

public ActionResult TestSchedule(int? selectedClassId)
{
    var registrationList = dbContext.Tbl_Registration
                                    .Where(r => r.Class_ID.HasValue);

    if (selectedClassId.HasValue && selectedClassId.Value > 0)
    {
        registrationList = registrationList.Where(r => r.Class_ID == selectedClassId.Value);
    }

    registrationList = registrationList.Select(r => new RegistrationWithClassDto
    {
        NameOfApplicant = r.NameOfApplicant,
        Father_Name = r.Father_Name,
        Father_Email = r.Father_Email,
        Father_MobileNumber = r.Father_MobileNumber,
        DateOfBirth = r.DateOfBirth,
        Created_On = r.Created_On,
        Picture = r.Picture,
        B_Certificate = r.B_Certificate,
        Class_Name = r.Tbl_Class.Class_Name
    });

    ViewBag.ClassList = dbContext.Tbl_Class
                                 .Select(c => new SelectListItem  
                                                  { 
                                                      Value = c.Class_ID.ToString(), 
                                                      Text = c.Class_Name
                                                  })
                                 .ToList();

    return View(registrationList.ToList());
}

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