如何在Controller的Details中关联第三表返回JsonResult？解决Join报错

我来帮你排查和修复这个问题，同时实现Details方法里的查询逻辑。先看你原代码里的几个关键错误：

• 表名拼写错误：第二个Join的表应该是CohortProgramEnrollments（复数形式），你写成了CohortProgramEnrollment
• 关联条件错误：
  • 关联BadgeAssignments和CohortProgramEnrollments时，应该用bad.CohortProgramEnrollmentID == coh.CohortProgramEnrollmentID，而不是和coh.CohortSubscriptionId关联
  • 关联BadgeAssignments和Badges时，应该用bad.BadgeID == des.BadgeID，而不是和des.Description关联
• Where条件逻辑错误：你需要通过CohortSubscriptionId过滤，所以应该判断coh.CohortSubscriptionId == 传入的参数，而不是bad.BadgeID == CohortProgramEnrollmentID
• 返回字段错误：Badges表不存在CohortProgramEnrollmentID字段，应该返回徽章自身的属性（比如BadgeID和Description）

修正后的GetBadgesStudentList方法 #

根据你的需求（通过CohortSubscriptionId获取学生的徽章），我调整了方法参数为cohortSubscriptionId，并修复了所有逻辑错误：

[HttpGet]
public JsonResult GetBadgesStudentList(int cohortSubscriptionId)
{
    db.Configuration.ProxyCreationEnabled = false;
    
    var badgesList = from bad in db.BadgeAssignments
                     join coh in db.CohortProgramEnrollments 
                         on bad.CohortProgramEnrollmentID equals coh.CohortProgramEnrollmentID
                     join des in db.Badges 
                         on bad.BadgeID equals des.BadgeID
                     where coh.CohortSubscriptionId == cohortSubscriptionId
                     select des; // 直接选择Badges实体
    
    // 提取需要返回的字段
    var result = badgesList.Select(x => new {
        x.BadgeID,
        x.Description
    }).ToList();
    
    return Json(result, JsonRequestBehavior.AllowGet);
}

在Details方法中实现徽章查询逻辑 #

接下来修改Details方法，查询当前CohortSubscriptionId对应的学生徽章，并将数据传递给视图：

[Authorize(Roles = "Administrator, Coach")]
public ActionResult Details(int? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    
    CohortSubscriptions cohortSubscriptions = db.CohortSubscriptions.Find(id);
    if (cohortSubscriptions == null)
    {
        return HttpNotFound();
    }
    
    // 查询该学生关联的所有徽章
    var studentBadges = from bad in db.BadgeAssignments
                        join coh in db.CohortProgramEnrollments 
                            on bad.CohortProgramEnrollmentID equals coh.CohortProgramEnrollmentID
                        join des in db.Badges 
                            on bad.BadgeID equals des.BadgeID
                        where coh.CohortSubscriptionId == id.Value
                        select new {
                            des.BadgeID,
                            des.Description
                        };
    
    // 将徽章数据传递给视图，可以用ViewBag或者ViewModel
    ViewBag.StudentBadges = studentBadges.ToList();
    
    return View(cohortSubscriptions);
}

额外建议 #

如果你的项目使用ViewModel，建议创建一个CohortSubscriptionDetailsViewModel类，包含CohortSubscriptions实体和List<Badge>属性，这样比用ViewBag更规范：

public class CohortSubscriptionDetailsViewModel
{
    public CohortSubscriptions CohortSubscription { get; set; }
    public List<Badge> StudentBadges { get; set; }
}

然后修改Details方法的返回逻辑：

var viewModel = new CohortSubscriptionDetailsViewModel
{
    CohortSubscription = cohortSubscriptions,
    StudentBadges = studentBadges.Select(x => new Badge { BadgeID = x.BadgeID, Description = x.Description }).ToList()
};

return View(viewModel);

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