Using Linq2SQL to return Parent Table Data, but sorted by Child Table Data

Question

I have a Parent/Child Table relationship with the ChildTable linking back to the ParentTable via the ParentId foreign key. There's multiple records in the ChildTable for each ParentTable record. For those of use you like rudimentary visuals, here's the diagram.

ParentTable
------------
+Id
Date

ChildTable
------------
+Id
ParentId
Date

What I'm trying to do is return only ONE record for each ParentTable item that's joined to the most recent ChildTable's date value (Note, not the Parent's Date value). The results would look like:

ParentTable::Id ParentTable::Foo ChildTable:Id ChildData::Foo ChildData::Date
--------------- ---------------- ------------- -------------- ---------------
55              Other Values     700           Other values   12/1/2010      
1               "                1000          "              11/30/2010
10              "                214           "              10/31/2010

It's important that the ChildData::Date is sorted descending.

This seems like it should be simple, but I'm struggling with the LinQ2SQL aspects of it. I'm still waiting for my "ah-ha" moment when I can think in Linq.

Here's the final answer, thanks to Winston:

var results = 
    from p in parent
    join c in (from c2 in child orderby c2.Date descending select c2)
    on p.Id equals c.ParentId into childGroup
    select new { ParentId=p.Id, ParentDate=p.Date, ChildDate=childGroup.First().Date } into NewResult
    orderby NewResult.Activity descending
    select NewResult

Answer 1

The following should work, I've tested it on some dummy data.

var results = 
    from p in parent
    join c in (from c2 in child orderby c2.Date descending select c2)
    on p.Id equals c.ParentId into childGroup
    select new { ParentId=p.Id, ParentDate=p.Date, ChildDate=childGroup.First().Date };

Updated to select only the fields required.

Answer 2

Try something like

var x = ParentTable.Select(z=> new {
z.Id,
z.Foo,
z.ParentIDChildTable.OrderBy(c=> c.Date).First().ID,
z.ParentIDChildTable.OrderBy(c=> c.Date).First().Foo,
z.ParentIDChildTable.OrderBy(c=> c.Date).First().Date

}

There is possibly a better way to order the Child table before selecting but i can't think of it... Also the foreign key may not be as typed above but intellisense should help you there.