Time Tortoise: Loading Related Entities with Entity Framework

Time Segment Filtering

This is one in a series of articles about Time Tortoise, a Universal Windows Platform app for planning and tracking your work schedule. For more on the development of this app and the ideas behind it, see my Time Tortoise category page.

When you click on an activity in Time Tortoise, the app displays the list of time segments associated with that activity. But you don’t normally want to see every time segment ever recorded for that activity. Instead, it makes more sense to show only the most recent ones, like the segments recorded for the current day. That’s the change I’m making this week.

The ObjectQuery.Include Method

As I explained in my original post on time segments, it’s easy to retrieve both activities and time segments in a single query:

return _context.Activities.Include(a => a.TimeSegments).ToList();

But this statement returns all of the time segments associated with each activity. What if I want a filtered list instead? Assuming I have a local DateTime variable called startTime with the desired date/time, will this work?

return _context.Activities
    .Include(a => a.TimeSegments.Where(t => t.StartTime >= startTime))
    .ToList();

It seems like it will. IntelliSense suggests the appropriate syntax, and the code compiles without any errors. But at runtime, I get this exception:

System.InvalidOperationException occurred. The property expression ‘a => {from TimeSegment t in [a].TimeSegments where ([t].StartTime >= __startTime_Value_0) select [t]}’ is not valid. The expression should represent a property access: ‘t => t.MyProperty’.

There are many Stack Overflow and GitHub discussions about this error message, and about the Include method in general. And there are ways to use Where to filter results. But I didn’t find a way to do exactly what I tried above: with a single statement, retrieve all activities along with a filtered subset of their associated time segments. As far as I can tell, there’s a limitation in the current version of Entity Framework Core that prevents this type of Include filtering.

Alternatives

Although the Include approach doesn’t seem to work as shown, it’s still possible to filter time segments in other ways.

One option is to retrieve all time segments and then filter them in memory. But that would likely lead to performance problems as the number of time segments increased. In my experience with time trackers, it’s common to create a lot of time segments. The benefit of a time tracker is that it can collect many small blocks of time, and tell you at the end of the day what they add up to. So we want a solution that scales to a large number of time segments.

Another option is to duplicate the functionality of Include by explicitly retrieving the time segments for each activity. That’s the approach I decided to take.

The original Include call shown above is in Repository.LoadActivities, which is called when the main view model is instantiated. This means that when the app starts, all activities and time segments are loaded. I could remove the Include and then modify LoadActivites to iterate through each returned activity, loading the time segments for each one. But that seems wasteful. The user may never look at most of those time segments.

Instead, I moved the time segment load operation so it occurs only when the user selects an activity. There’s now a separate database call to retrieve each time segment list, so this design ensures that the user really wants to see the time segments before the call is made.

CalendarDatePicker

Now that we have a way to filter time segments by date, we need a way for the user to provide the date range to filter on.

When I wrote about input validation a few weeks ago, I mentioned that I prefer typing a date/time value to selecting one. That’s definitely true when manually adding a time segment. For filtering based on a date range, the choice isn’t as clear. Selecting a recent date (like “last Monday”) often requires just two clicks, one to open the calendar and one to select a date. So I decided to try out the CalendarDatePicker control to see how it works.

Like other XAML input controls, CalendarDatePicker supports data binding. I bound its Date field to a new property in the main view model, DateRangeStart. The property data type is DateTimeOffset, and it must be nullable because CalendarDatePicker.Date is null when no date is selected. DateTimeOffset can be conveniently converted to DateTime (if you don’t worry too much about time zones).

Once the date value is in the view model, it’s available to pass to the repository method that will retrieve the appropriate time segments. But we need a trigger to tell the view model to make that repository call. One option is to add a refresh button to the UI, but that’s rather old-fashioned. I also tried using the DateChanged event on the CalendarDatePicker. It fires when the user selects a new date, which is a logical time to filter the time segments for the current activity. But I found that the event fires before the data binding happens, so it ends up with the old date value. To avoid this problem, I moved the LoadTimeSegments call to the DateRangeStart field. So after the binding code runs, the new time segments are retrieved.

A Reminder about INotifyPropertyChanged

Before I started working on time segment filtering this week, I noticed a bug: when I typed in the time segment start/end text boxes, the values in the time segment list didn’t update. It seemed like a data binding bug (which it was).

I mentioned last week that I created two special properties called SelectedTimeSegmentStartTime and SelectedTimeSegmentEndTime to help with binding to nullable values. These properties are “special” because, unlike many public view model properties, they don’t correspond directly to private properties. There are no private _selectedTimeSegmentStartTime and _selectedTimeSegmentEndTime variables.

Instead, these properties expose the start and end times of the selected time segment, or if that time segment is null, they expose an empty string. That’s to prevent the exception that would occur if the view tried to use a null time segment object for binding purposes.

For “normal” view model properties, NotificationBase provides a method called SetProperty that simplifies the accessor code. For example, here’s the property used for binding to the start/stop button text:

private string _startStopText = "Start";
public string StartStopText
{
    get => _startStopText;
    private set => SetProperty(ref _startStopText, value);
}

But for properties that don’t have a private backing field, SetProperty doesn’t work, since there’s nothing to set. However (and this is where the bug happens) any field used in data binding needs to raise the PropertyChanged event when its data changes. Otherwise, the UI doesn’t know to update itself. SetProperty takes care of this, but fields that don’t use SetProperty need to do it themselves. So that was the bug fix in the special properties:

RaisePropertyChanged("SelectedTimeSegmentStartTime");

and

RaisePropertyChanged("SelectedTimeSegmentEndTime");

Reminder: XAML data binding relies on INotifyPropertyChanged. If the UI isn’t updating, it’s probably because a field isn’t properly implementing that interface.