CODEWITHSUNDEEP
c#entity-frameworklinq
Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

How can I get all the children records in a single query if I need to lookup the parent ID?

I have a model with a parent - child relationship. Year and Week

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

So if I have YearSelected, to get all the weeks from a year I do:

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

But in DB I only save WeekID so only have model.WeekSelected

So How I get all the week of the same Year as model.WeekSelected

I can do a query first to get the YearID, but wonder if can do it in a single line.

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

Upvotes: 0

Views: 246

Answers (1)

NetMage
NetMage

Reputation: 26907

You will need to use a sub-query to get the YearID:

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

This produces simpler SQL, not sure if it makes a performance difference, when you know WeekSelected only matches one row:

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

Upvotes: 1

Related Questions