How can I do this with one QueryOver statement

Question

This statement is using a foreach which I am trying to get rid of:

CBTAppointmentDto app = null;
ModifyAppointmentRequest alias = null;
Domain.PearsonVue.TestCenter center = null;
Exam exam = null;

var result = Session.QueryOver(() => alias)
                    .Where(x => x.Candidate.Id == candidateId)
                    .Where(x => x.EventType == "ApptCreated")
                    .JoinAlias(x => x.Exams, () => exam)
                    .JoinAlias(() => alias.TestCenter, () => center)
                    .SelectList(list => list
                                 .Select(() => exam.ExamName).WithAlias(() => app.TestName)
                                 .Select(() => exam.ExamSeriesCode)
                                                    .WithAlias(() => app.ExamSeriesCode)
                                 .Select(() => alias.AppointmentStartTime)
                                                    .WithAlias(() => app.TestDate)
                                 .Select(() => center.TestCenterName)
                                                    .WithAlias(() => app.TestCenterName))
                    .TransformUsing(Transformers.AliasToBean<CBTAppointmentDto>())
                    .List<CBTAppointmentDto>().ToList();

foreach (var cbtAppointmentDto in result)
{
     var session = Session.QueryOver<TestSession>()
                          .Where(x => x.SessionName == cbtAppointmentDto.ExamSeriesCode)
                          .SingleOrDefault();

     if (session == null) continue;
        cbtAppointmentDto.TestStartDate = session.TestsStartDate;
        cbtAppointmentDto.TestEndDate = session.TestsEndDate;
}

return result;

Is there a way to do it with aQueryOver statement only?

Any suggestions ?

Answer 1

to save roundtrips use batch selects with ToFutureValue

var sessions = results.Select(cbtAppointmentDto => Session.QueryOver<TestSession>()
                         .Where(x => x.SessionName == cbtAppointmentDto.ExamSeriesCode)
                         .FutureValue()).ToOList();

for (int i = 0; i < sessions.Count; i++)
{
    var session = sessions[i].Value;
    if (session != null)
    {
        results[i].TestStartDate = session.TestsStartDate;
        results[i].TestEndDate = session.TestsEndDate;
    }
}