Left Join on Date, Grouping Results, Display all Dates, even if empty, in grouped result

Question

I working with MVC 5/ EF 6. I have been trying to create a view that displays a calendar for multiple people.

I already have the view grouped by person, and I show a list of all dates within the search range. But if the person has nothing scheduled for that date, the date is not being listed under the person.

Right now, the empty dates with no one scheduled for anything are being grouped together under an "empty" person group.

My current results:

Person: 
-------------------------------
04/01/16 (blank)
04/02/16 (blank)
04/03/16 (blank)

Person: Jane
-------------------------------
04/04/2016: To Do Item
04/05/2016: To Do Item

Person: John
-------------------------------
04/04/2016: To Do Item
04/05/2016: To Do Item

How can I get this result?

Person: Jane
-------------------------------
04/01/16 (blank)
04/02/16 (blank)
04/03/16 (blank)
04/04/2016: To Do Item
04/05/2016: To Do Item

Person: John
-------------------------------
04/01/16 (blank)
04/02/16 (blank)
04/03/16 (blank)
04/04/2016: To Do Item
04/05/2016: To Do Item

Query returned to view

var activecal = db.Calendar.Where(x => (x.CalDate >= startdate && x.CalDate <= enddate).ToList();


// merge calendar with date range in search selected 

var calendar = (from d in dateRange //dateRange = date range search
                    join c in activecal.ToList() on d equals c.CalDate into joinedResult
                    from r in joinedResult.DefaultIfEmpty()
                    select new CalVM
                    {
                      FullName = r == null ? null : r.FullName,
                      CalLocation = r == null ? null : r.CalLocation,
                      calDay = d.Date,                
                    }).ToList();

View

@{
        foreach (var group in Model.GroupBy(a => a.FullName))
        {
            <h3>Person: @group.Key</h3>
            <div class="table-responsive">
                <table class="table table-hover small">
                    <tr>
                        <th>
                            Cal Day
                        </th>
                        <th>
                            Location
                        </th>
                    </tr>
                    @foreach (var i in group)
                    {
                        <tr>
                            <td>
                                @Html.DisplayFor(modelItem => i.calDay) 
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => i.CalLocation)
                            </td>                         
                        </tr>
                    }
                </table>
            </div>
        }
    }

I understand that I have to somehow get the Person's name associated with all the dates in the dateRange list, I am just not sure how to do it. The only parameter in the date range list is a date, so there is nothing to join on.

Answer 1

You could GroupBy the person and then for each Date of your range you would keep the Date and the scheduled events (I named it ToDo) for that date. And I guess you can have more than one ToDo per date.

Example:

var dateRange = GetDateRange(new DateTime(2016, 1, 1), new DateTime(2016, 1, 5)).ToList();

var todos = new List<ToDo>
{
    new ToDo { Date = new DateTime(2016,1,3), Person = "John", What = "Do that"},
    new ToDo { Date = new DateTime(2016,1,4), Person = "John", What = "Do this"},
    new ToDo { Date = new DateTime(2016,1,4), Person = "John", What = "Do nothing"},

    new ToDo { Date = new DateTime(2016,1,2), Person = "Jane",What = "Do something"},
    new ToDo { Date = new DateTime(2016,1,3), Person = "Jane", What = "Do whatever"}
};

var personToDos = todos.GroupBy(x => x.Person)
    .ToDictionary(key => key.Key, value => dateRange.Select(date => new ToDosPerDate
    {
        Date = date,
        ToDos = value.Where(a => a.Date == date)
    }));

foreach (var item in personToDos)
{
    Console.WriteLine(item.Key);
    Console.WriteLine("---------------");
    foreach (var model in item.Value)
    {
        Console.Write(model.Date + " ");
        Console.WriteLine(string.Join(", ", model.ToDos));
    }
    Console.WriteLine();
}

And the other stuff:

public class ToDo
{
    public DateTime Date { get; set; }

    public string Person { get; set; }

    public string What { get; set; }

    public override string ToString()
    {
        return What;
    }
}

public class ToDosPerDate
{
    public DateTime Date { get; set; }

    public IEnumerable<ToDo> ToDos { get; set; }
}

// From: http://stackoverflow.com/questions/3738748/create-an-array-or-list-of-all-dates-between-two-dates
public static IEnumerable<DateTime> GetDateRange(DateTime startDate, DateTime endDate)
{
    if (endDate < startDate)
        throw new ArgumentException("endDate must be greater than or equal to startDate");

    while (startDate <= endDate)
    {
        yield return startDate;
        startDate = startDate.AddDays(1);
    }
} 

The output of the above code is:

John
---------------
2016-01-01 12:00:00 AM
2016-01-02 12:00:00 AM
2016-01-03 12:00:00 AM Do that
2016-01-04 12:00:00 AM Do this, Do nothing
2016-01-05 12:00:00 AM

Jane
---------------
2016-01-01 12:00:00 AM
2016-01-02 12:00:00 AM Do something
2016-01-03 12:00:00 AM Do whatever
2016-01-04 12:00:00 AM
2016-01-05 12:00:00 AM

Sample to output personToDos in a MVC Razor view:

@model Dictionary<string, IEnumerable<ToDosPerDate>>
...
@foreach (var person in Model)
{
    <h3>@person.Key</h3>

    foreach (var d in person.Value)
    {
        <div>Date: @d.Date</div>

        foreach (var todo in d.ToDos)
        {
            <div>@todo.What</div>
        }
    }
}