Selecting/Filtering nested collections in L2E

Consider 3 entities. Group, Box and Message. Each 开发者_开发技巧Group contains Boxes and each Box contains Messages.

what's the neatest way to retrieve Messages inside a Group with custom conditions in Linq To Entitis using lambda expressions?

For example, I want to select messages submitted yesterday in a certain group (thus, the resultr should be IEnumerable<Message>). How can I make the fewest calls to db and filter out results in my db-query?

Update

Got a downvote for not showing my code. That's because I don't have any. Question is simple enough. not a homework. Didn't expect that!

Assuming you have foreign keys in your database and Navigation properties in your model because of this, the following code should do the trick:

var messages = context.Groups
                      .SelectMany(g => g.Boxes
                                        .SelectMany(b => b.Messages
                                                          .Where(m => m.Date ==
                                                                      yourDate)
                                                   )
                                 );

You just need to put join query between them

from g in group 
join b in boxes on g.id = b.groupid
join m in messages on b.id = m.boxid
where m.Date = your date
select m