C # interpolation date range using Linq

Question

I have an Event class with a start and end date and specific days of the week when an event can occur:

public class Event { public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } public DayOfWeek[] DayOfWeekList { get; set; } public string Title { get; set; } } 

I can have events that happen once:

  Event 1: StartDate = 9/15/2013, EndDate = 9/15/2013, DOW = Sunday 

I may have events with several days:

  Event 2: StartDate = 9/15/2013, EndDate = 9/16/2013, DOW = Sunday, Monday 

I can have events that occur in a date range, but only specific, possibly endless days of the week, for example, during September, but only on Tuesday and Thursday:

  Event 3: StartDate = 9/1/2013, EndDate = 9/30/2013, DOW = Tuesday, Thursday 

Given a date range of 9/1 - 9/16, I would like to display events as a single event day record in the grid as follows:

  Event 3: 9/3/2013 Tuesday
 Event 3: 9/5/2013 Thursday
 Event 3: 10/10/2013 Tuesday
 Event 3: 12/12/2013 Thursday
 Event 1: 9/15/2013 Sunday
 Event 2: 9/15/2013 Sunday
 Event 2: 9/16/2013 Monday

I will only store one event record in db to represent the days of the event instead of one record for each day. Therefore, for the date range above, I will get 3 event records from db and you will need to generate a sequence of event records for this date range according to the schedule, using the days of the week.

I will use Linq to SQL to retrieve 3 event records, but then you need to expand each event schedule to create new event records, one for each day. This seems less effective, especially if the site is widely used.

Can this be done with Linq?

An alternative is to have separate child records for each day the event occurs in db, but it will be a pain to maintain if someone changes the schedule from every Monday to every Tuesday for exaple. We can also have events without a specific end date.

Answer 1

This is a little brute force solution, but something like this should work:

 var startDate = new DateTime(2013, 9, 1); var endDate = new DateTime(2013, 9, 16); var totalDays = (int)(endDate - startDate).TotalDays + 1; var results = from i in Enumerable.Range(0, totalDays) let d = startDate.AddDays(i) from e in eventList where e.StartDate <= d && e.EndDate >= d && e.DayOfWeekList.Contains(d.DayOfWeek) select new { Event = e, Date = d }; 

This gives the correct results when I tested it in memory. Unfortunately, this probably will not translate well into pure Linq-to-SQL code. I believe that it is best to materialize a subset of events first, and then use a modified query to create the final set of results in memory:

 var eventList = (from e in db.Events where e.StartDate <= endDate && e.EndDate >= startDate select e) .AsEnumerable(); var results = from i in Enumerable.Range(0, totalDays) let d = startDate.AddDays(i) from e in eventList where e.StartDate <= d && e.EndDate >= d && e.DayOfWeekList.Contains(d.DayOfWeek) select new { Event = e, Date = d }; 

Answer 2

If you most often read events and do not mind a small additional saving, you can choose to materialize your events based on the rules in the new table. This table can be cleaned and restored if you want, based on the rules, or selectively re-populated as necessary.

Event 1 on saving will create one record with EventID: 1

Event 2, when saved, will create entries for the date range corresponding to the DOW property, with EventID: 2

Event 3 will do the same, but with its date range / DOW, with EventID: 3

You can use some fancy LINQ to determine which rows to add if you want, but the end result is that you need rows of physical instances for a very simple query.

When updating an event, you can simply delete all rows for this EventID and re-create them based on the changes.

Given the above, your read operation may be simple:

 EventInstances.Where(i => i.Date >= startDate && i.Date <= endDate); 

If you need slow / complex reads and fast writes, you will need to create this memory card every time you read.

UPDATE: some code to show what I mean

The logic for populating a table is the same logic that you could use to create a table in memory:

 // slightly updated Event class public class Event { public int ID { get; set; } public DateTime StartDate { get; set; } public DateTime? EndDate { get; set; } public DayOfWeek[] DayOfWeekList { get; set; } public string Title { get; set; } } var startDate = new DateTime(2013, 9, 1); var endDate = new DateTime(2013, 9, 16); var totalDays = (int)endDate.Subtract(startDate).TotalDays + 1; // sample data, including a 4th event starting a while ago with no end date var events = new List<Event> { new Event { ID = 1, Title = "Event 1", StartDate = new DateTime(2013, 9, 15), EndDate = new DateTime(2013, 9, 15), DayOfWeekList = new[] { DayOfWeek.Sunday } }, new Event { ID = 2, Title = "Event 2", StartDate = new DateTime(2013, 9, 15), EndDate = new DateTime(2013, 9, 16), DayOfWeekList = new[] { DayOfWeek.Sunday, DayOfWeek.Monday } }, new Event { ID = 3, Title = "Event 3", StartDate = new DateTime(2013, 9, 1), EndDate = new DateTime(2013, 9, 30), DayOfWeekList = new[] { DayOfWeek.Tuesday, DayOfWeek.Thursday } }, new Event { ID = 4, Title = "Event 4", StartDate = new DateTime(2013, 1, 1), EndDate = null, DayOfWeekList = new[] { DayOfWeek.Wednesday } }, }; var eventsInRange = events .Where(e => e.StartDate <= endDate) .Where(e => (e.EndDate == null || e.EndDate.Value >= startDate )) // if you are getting from the database, force this data to be // retrieved since the following section would not work with the DB .AsEnumerable(); var daysInRange = Enumerable .Range(0, totalDays) .Select(i => startDate.AddDays(i)); var eventInstances = daysInRange .SelectMany(d => eventsInRange .Where(e => e.EndDate == null || d <= e.EndDate.Value) .Where(e => d >= e.StartDate) .Where(e => e.DayOfWeekList.Contains(d.DayOfWeek)) .Select(e => new { Date = d, Day = d.DayOfWeek, Event = e })); 

If you want to fill out a table with this data to simplify queries, simply set the start date and end date based on a reasonable one (for example, start 1 year ago, at the end of 2 years).

If you want to re-populate only one event after the update, simply delete all entries for this event ID and eventsInRange only update the event.