如果需要查找父ID，如何在单个查询中获取所有子记录？

Question

我有一个父母/子女关系的模型。Year和WeekYears: YearID, NameWeeks: WeekID, YearID, Name因此，如果我有YearSelected，要获得一年中的所有周数，我会：List<Week> weeks = db.weeks.Where(w => w.YearID == model.YearSelected).ToList();model.WeekList = new SelectList(weeks, "WeekID", "Name");但是在数据库中我只保存WeekID所以只有model.WeekSelected所以我如何获得同年的同一周 model.WeekSelected我可以先查询以获取YearID，但想知道是否可以在一行中完成。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();

当您知道WeekSelected只匹配一行时，这将生成更简单的SQL，不确定是否会对性能产生影响：

List<Week> weeks = db.weeks

    .Where(w => w.YearID == db.weeks.Single(w => w.WeekID == model.WeekSelected).YearID)

    .ToList();