How can I "expand" IQueryable results based on a range of values?

Question

I am trying to determine a way using Linq within the IQueryable, to "expand" an entity into multiple query results based on a range of values. Specifically, expand the results by every day between a begin and end date, inclusive.

The following are the entities of concern (which have been slimmed-down and had annotations removed for clarity):

public class Location {
    public int Id { get; set; }
    public string Name { get; set; }
}

public class WorkAssignment {
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public virtual Location Location { get; set; }
    public int LocationId { get; set; }
}

For example's sake, let's say we have:

Locations:

Id    Name
 1    Habitat
 2    Church

Work Assignments:

Id   Name         Start         End      LocationId
 1   Jim       29-Apr-2019   3-May-2019      1
 2   Natasha    4-May-2019   7-May-2019      2
 3   Jennifer   5-May-2019   6-May-2019      2

I'd like to obtain the following using Linq, preferably using fluent interface methods. It is important to perform the "expansion" within the query (making the DB responsible). The full data set and referenced entities is large and I do not wish to materialize using .ToList() and foreach statements.

WorkerName    Date      LocationId   LocationName
Jim        29-Apr-2019      1          Habitat
Jim        30-Apr-2019      1          Habitat
Jim         1-May-2019      1          Habitat
Jim         2-May-2019      1          Habitat
Jim         3-May-2019      1          Habitat
Natasha     4-May-2019      2          Church
Natasha     5-May-2019      2          Church
Natasha     6-May-2019      2          Church
Natasha     7-May-2019      2          Church
Jennifer    5-May-2019      2          Church
Jennifer    6-May-2019      2          Church

The ultimate goal is to obtain the above "expansion" in order to group by Location (ordered on LocationName) then group on Date (ordered ascending) and then by Name to provide sign-in sheets for the worker. The sign-in sheets are grouped by location, then by date. Each sheet will list the workers for a specific location for a specific date.

I've got the grouping query figured out (thanks to Jon Skeet and all of his awesome SO answers). I just need help with the above expansion, if it's even possible to do within the IQueryable.

Answer 1

One solution would be to utilize a calendar or date dimension table (https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) in combination with a view to represent your date range to return an entity that represents each date within the range.

For instance:

public class WorkAssignment {
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public virtual Location Location { get; set; }
    public int LocationId { get; set; }
}

[Table("vwWorkAssignmentByDate")]
public class WorkAssignmentDate 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public virtual Location Location { get; set; }
    public int LocationId { get; set; }
    public DateTime Date { get; set; }
}

Note that WorkAssignmentDate does not extend WorkAssignment. The reason for this is that if you extend it, when querying WorkAssignment directly EF will attempt to NOT IN against the view which is both a performance sink as well as can cause issues when attempting to load a single row. (There may be some form of mapping that can avoid this behaviour) You can set the Id as the PK for the view-based entity to keep EF happy, but it will not be a unique row identifier. (so no updates! Not an issue because this is feeding from a view anyways)

Where vwWorkAssignment is a view that joins WorkAssignment to the DateDimension table:

SELECT wa.*, dd.Date
FROM dbo.WorkAssignment wa 
INNER JOIN dbo.DateDimension dd 
  ON dd.Date >= wa.StartDate AND dd.Date <= wa.EndDate

The WorkAssignmentDate entity would need to be treated as a read-only entity. To update a work assignment you would load and use the WorkAssignment entity by ID.