LINQ to SQL query against joined tables with criteria from both tables resulting in hierarchical object model

Question

We have a calendaring application in which each appointment can have multiple persons associated with it.

Appointment 1 <--> * AppointmentPerson

Appointment
  Date
  ID

AppointmentPerson
  AppointmentID (FK to Appointment.ID)
  PersonID

The simplest query is to find all appointments for a given person and date. Using LINQ to SQL we can say:

 ... FindAppointments(Date date, string personID)

 var q = from appt in dc.Appointments
         join ap in dc.AppointmentPerson on appt.ID equals ap.AppointmentID
         where appt.Date == date && ap.PersonID == personID
         select new { appt, ap };

This gives us the "flattened" result, where each row has an Appointment and an AppointmentPerson record. We want a result that instead gives us a list of Appointments with a child property that gives the list of AppointmentPersons.

The foreign key relationship between AppointmentPerson.AppointmentID and Appointment.ID is defined in the schema, so the LINQ ORM generates the entity class that we want:

  [generated by LINQ to SQL]...
  class Appointment
  {
     ...
     EntitySet<AppointmentPerson> AppointmentPersons

but if we query against appointments, we can only query on date, not on personID...

    var q = from appt in dc.Appointments
            where appt.Date == dateTime && [invalid]appt.AppointmentPersons.PersonID == personID[/invalid]
            select appt;

How would we write the invalid part of the query above?

If it's relevant, we need to eagerly load:

DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Appointment>(a => a.AppointmentPersons);

This question related to: http://stackoverflow.com/questions/3231901/linq-to-sql-querying-against-a-view-to-populate-an-object-model