0
Answer

Left Join on Date, Grouping Results, Display all Dates, even

Rohit Mane

Rohit Mane

8y
364
1

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.

Next Recommended Forum