0

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;
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • 1
    It would be better for everyone (you and the person coming after you who has to read your code) to do it in two lines instead of one. Is there any compelling reason to do it in a single line? – Robert Harvey Apr 25 '18 at 17:54
  • In any case, what you're looking for is a subquery. See https://stackoverflow.com/questions/418609 and http://technologycraftsmen.net/blog/2010/04/21/how-to-subqueries-in-linq-to-sql/ – Robert Harvey Apr 25 '18 at 17:55
  • @RobertHarvey is my understanding if I do separated line will need two call to db? so Is matter of performance. – Juan Carlos Oropeza Apr 25 '18 at 17:56
  • 1
    It's two queries in either case. – Robert Harvey Apr 25 '18 at 17:57
  • @RobertHarvey Well that is weird. Cant you do a join or something? In regular sql you can do it is a single query. – Juan Carlos Oropeza Apr 25 '18 at 17:58
  • ***If you're using Linq to Entities,*** it might be possible for the query engine in Entity Framework to optimize it. The only way to know is try it both ways, look at the resulting SQL that is generated, and see what SQL Server's execution plan is for each. My guess is that the subquery will actually de-optimize the generated SQL. Note that you can chain `IQueryable`s together and they work the same way as if they were written as a single statement. – Robert Harvey Apr 25 '18 at 17:59
  • `In regular sql you can do it is a single query.` -- No, it's still two queries, though SQL Server *might* be able to perform some optimizations on the subquery. Historically, subqueries are poor performers; in practice, it's probably better to get the ID first, and then run your simpler query. – Robert Harvey Apr 25 '18 at 18:01
  • You can probably use join query. But when asking EF related question, it's better to post the sample entity classes, because the answer really depends on what navigation properties are available. – Ivan Stoev Apr 25 '18 at 18:02
  • By the way, if your database is properly optimized with indices, the cost of getting that ID in its own query is very low. – Robert Harvey Apr 25 '18 at 18:03
  • @RobertHarvey The way I understand it, OP is asking for single database *trip*. – Ivan Stoev Apr 25 '18 at 18:05
  • @IvanStoev: Yes, I know what he meant. – Robert Harvey Apr 25 '18 at 18:05
  • Software developers are notoriously bad at working out the relative performance of code just by looking at it. Measure the performance of each approach, and then decide. – Robert Harvey Apr 25 '18 at 18:05
  • Also, note that you're not strictly limited to Linq to Entities for accomplishing this. Entity Framework has an `ExecuteSql` method; executing raw SQL might work better in this particular scenario. See https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx – Robert Harvey Apr 25 '18 at 18:07
  • 1
    I think it's not about performance, but for convenience. To me the problem is not the goal, but looking to the problem as SQL. While with proper EF model, the answer would be simply `db.Weeks.Where(w => w.WeekID == model.WeekSelected).SelectMany(w => w.Year.Weeks)` – Ivan Stoev Apr 25 '18 at 18:12
  • @IvanStoev: The *convenient* way is to use two queries. – Robert Harvey Apr 25 '18 at 18:14

1 Answers1

1

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();
NetMage
  • 26,163
  • 3
  • 34
  • 55