如果我需要查找父ID，该如何将所有孩子记录在单个查询中

Question

我有一个带有父母关系的模型。Year和Week

Years: YearID, Name
Weeks: WeekID, YearID, Name

所以，如果我有YearSelected，请从一年开始的所有几周开始：

List<Week> weeks = db.weeks.Where(w => w.YearID == model.YearSelected).ToList();
model.WeekList = new SelectList(weeks, "WeekID", "Name");

但是在db中，我只保存 WeekID，所以只有 model.WeekSelected

所以我在同一周与model.WeekSelected

获得如何获得

我可以首先进行查询以获取年度ID，但想知道是否可以单行进行。

int YearID = db.weeks.Where(w => w.WeekID == model.WeekSelected)
                     .SingleOrDefault().YearID.Value;

Answer 1

您需要使用子查询来获取YearID：

List<Week> weeks = db.weeks
    .Where(w => w.YearID == db.weeks.First(w => w.WeekID == model.WeekSelected).YearID)
    .ToList();

这会产生更简单的SQL，不确定它是否会差异，当您知道WeekSelected仅与一行匹配时：

List<Week> weeks = db.weeks
    .Where(w => w.YearID == db.weeks.Single(w => w.WeekID == model.WeekSelected).YearID)
    .ToList();