如何分组/聚合但返回聚合字段以外的字段?

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
  }

另一种选择是加载每个项目的所有里程碑,然后使用LinqtoObjects进行筛选:

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()
}

在我的头顶上:

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()
        };