Need help with LINQ query that joins two tables containing periodic events

Question

My real-life example is too obscure to explain, but this is a pretty good approximation of what I'm trying to do...

Month table has columns: Id, Name

Holiday table has columns: Id, MonthId, DayOfMonth, Name

Appointment table has columns: Id, MonthId, DayOfMonth, Description

How do I produce a list of unique events (holidays and appointments) ordered by the month and the day of month?

Sample results:

Month  Holiday     Day  Appointment  Day
----------------------------------------
Nov                     Fly to NYC   25
Nov    T-Giving    26
Nov                     Fly home     29
Dec    Xmas        25

So, I want separate columns for holidays and events, but I want them all to be unique and listed in order of month-day.

Here's what I have so far (see inline comments):

var events =
    from
        m in GetMonths()
    join
        h in GetHolidays()
        on m.Id equals h.MonthId
    join
        a in GetAppointments()
        on m.Id equals a.MonthId
    where
        //something that narrows all combinations of events to only unique events
    orderby
        m.Id,
        // something that interleaves h.DayOfMonth with m.DayOfMonth
    select
        new
        {
            Month = m.Name,
            Holiday = h.Name,
            HolidayDay = h.DayOfMonth,
            Appointment = a.Description,
            AppointmentDay = a.DayOfMonth
        };

Answer 1

You need to do LEFT OUTER joins in SQL the LINQ would look something like this (untested):

var events =
    from
        m in GetMonths()
    groupjoin
        h in GetHolidays()
        on m.Id equals h.MonthId
        into hol = group
    from
        h in hol.DefaultIfEmpty()
    groupjoin
        a in GetAppointments()
        on m.Id equals a.MonthId
        into appt = group
    from
        a in appt.DefaultIfEmpty()
    where
        //something that narrows all combinations of events to only unique events
    orderby
        m.Id,
        // something that interleaves h.DayOfMonth with m.DayOfMonth
    select
        new
        {
            Month = m.Name,
            Holiday = h.Name,
            HolidayDay = h.DayOfMonth,
            Appointment = a.Description,
            AppointmentDay = a.DayOfMonth
        };

Answer 2

Here is an alternate answer using a UNION instead of a LEFT OUTER that returns exactly the result set you are looking for (I don't think my first answer would quite meet your "unique" requirement):

var a = from m in month
        join h in holiday on m.Id equals h.MonthId
        select new
        {
            MonthId = m.Id,
            Month = m.Name,
            Holiday = h.Name,
            HolidayDay = h.DayOfMonth,
            Appointment = "",
            AppointmentDay = 0

        };

var b = from m in month
        join p in appointments on m.Id equals p.MonthId
        select new
        {
            MonthId = m.Id,
            Month = m.Name,
            Holiday = "",
            HolidayDay = 0,
            Appointment = p.Description,
            AppointmentDay = p.DayOfMonth
        };

var events = from o in a.Union(b)
            orderby o.MonthId, o.HolidayDay + o.AppointmentDay
            select o;