[SQL/Linq to SQL] How do I group/aggregate but return fields other than the aggregation field?

Question

I have two tables, Tasks and TaskMilestones. I want a query to return the most recent past milestone and the nearest future TaskMilestone for each Task. I'm working with C#/LINQ-to-SQL. How do I go about this?

Task columns: Id, TaskName TaskMilestones columns: Id, TaskId, MilestoneName, MilestoneDate

I want a return table with rows containing: TaskName, MilestoneDate, MilestoneName

My current solution causes Linq to query the database once for each Task, which is unacceptably slow.

[EDIT to address comments] The current implementation is simple and not a single statement, it just queries the list of Tasks and then queries for each TaskId twice with proper where clauses:

var x = from p in this.Database.Task
        join pm in this.Database.TaskMilestones on p.Id equals pm.TaskId
        select new
        {
            TaskId = p.Id,
            TaskName = p.Name,
            MilestoneName = m.Name,
            MilestoneDate = pm.MilestoneDate,
        };

foreach (var record in records)
{
    var y = x.Where(p => p.TaskId == record.Id && p.MilestoneDate <= dt);
    var z = x.Where(p => p.TaskId == record.Id && p.MilestoneDate > dt);

    ...

Answer 1

Off the top of my head:

var q = from t in Context.Tasks
        let mostRecentDate = t.Milestones.Where(m => m.MilestoneDate < DateTime.Now)
                                         .Max(m => m.MilestoneDate)
        select new 
        {
            TaskId = t.Id,
            TaskName = t.TaskName,
            RecentPastMilestone = t.Milestones.Where(m => m.MilestoneDate == mostRecentDate)
                                              .FirstOrDefault()
        };

Answer 2

DateTime dt = DateTime.Today;

var records =
  from p in db.Tasks
  let pastMilestone = p.TaskMilestones
    .Where(pm => pm.MilestoneDate <= dt)
    .OrderByDescending(pm => pm.MilestoneDate)
    .FirstOrDefault()
  let nextMilestone = p.TaskMilestones
    .Where(pm => pm.MilestoneDate > dt)
    .OrderBy(pm => pm.MilestoneDate)
    .FirstOrDefault()
  select new
  {
    Task = p,
    PastMilestone = pastMilestone,
    NextMilestone = nextMilestone
  }

---

Another option is to load all of the milestones for each project, then filter them using LinqToObjects later:

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Task>(p => p.TaskMilestones);
db.LoadOptions = dlo;

var records = db.Tasks;

foreach(Task record in records)
{
  TaskMilestone pastMilestone = record.TaskMilestones
        .Where(pm => pm.MilestoneDate <= dt)
        .OrderByDescending(pm => pm.MilestoneDate)
        .FirstOrDefault()
  TaskMilestone nextMilestone = record.TaskMilestones
        .Where(pm => pm.MilestoneDate > dt)
        .OrderBy(pm => pm.MilestoneDate)
        .FirstOrDefault()
}