如果需要查找父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");

但是在数据库中我只保存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&nbsp; &nbsp; .Where(w => w.YearID == db.weeks.First(w => w.WeekID == model.WeekSelected).YearID)&nbsp; &nbsp; .ToList();当您知道WeekSelected只匹配一行时，这将生成更简单的SQL，不确定是否会对性能产生影响：List<Week> weeks = db.weeks&nbsp; &nbsp; .Where(w => w.YearID == db.weeks.Single(w => w.WeekID == model.WeekSelected).YearID)&nbsp; &nbsp; .ToList();