Efficiently persisting calendar data with Entity Framework?

I am working on a school project which involves managing driving instructor calendars.

The application stores the time periods over which an instructor is available.

public partial class AvailabilityPeriod // kind of pseudo code
{
  Instructor instructor;
  DateTime start;
  DateTime end;
}

It also stores the individual appointments for this instructor, in a similar fashion.

When a customer says, for example, "I want a 2-hour 开发者_开发问答lesson", I have to fetch availability periods and appointements of all instructors in order to compute their "actual availability" and then find someone having more than 2 hours free in his/her schedule.

Is there a better way ?

My question is almost an exact duplicate of Time Calendar Data Structure and I know it. But, well... We're in 2011 and I'm interested in Entity Framework-specific info, or at least something about doing this whith object relational mapping :-)

---

I wrote a similar scheduling application, and I ended up taking a little different approach (though your approach will certainly work).

In that system, the schedule was broken down into 15 minute increments, called blocks, and stored in a table. Everyone was always available unless they specifically requested the time off. These time off requests were stored in a separate table, again in 15 minute increments.

We had a ScheduleBlock table that basically stored all of the potential blocks which was populated based off of business rules, and a ScheduleException table that stored the blocks that users could not work.

With this table structure, it was easy to create a UI that showed all the ScheduleBlock entities (this is all of the potential available time, i.e. the business hours), and then additionally bind all of the ScheduleException entities (appointments, PTO, sick days, etc) to show the time users could not work (color coded per user). From there, the user of the system could select the blocks of time they wanted to schedule their particular event, similar to the way MS Outlook works.

When we did automatic scheduling, like in your case, we queried for users having X number of block available for a specified date range, sorted by the number of appointments that they had, and looped through them looking for a match. We sorted by the number of appointments so that the person with the most open schedule would be scheduled first.

Hope this helps!