CODEWITHSUNDEEP
c#linq
Michael Brown
Michael Brown

Reputation: 9153

Get a range of dates given an interval and step

Edit It looks like creating a table that holds the DateTimes by minutes to join against would make the most sense. 100 years worth of minutes is ~52M rows. Indexed by the Ticks should make the query run pretty fast. It now becomes

Thanks for the feedback everyone!

I have a class called Recurrence that looks like this:

public class Recurrence
{
    public int Id { get; protected set; }
    public DateTime StartDate { get; protected set; }
    public DateTime? EndDate { get; protected set; }
    public long? RecurrenceInterval { get; protected set; }

}

It is an entity framework POCO class. There are two things I want to do with this class all with standard query operators. (So that the query runs entirely server side).

First I want to create a query that returns all the dates from the start date to the end date inclusive with the given recurrence interval. The iterative function is simple

for(i=StartDate.Ticks; i<=EndDate.Ticks; i+=RecurrenceInterval)
{
  yield return new DateTime(i);
}

Enumerable.Range() would be an option but there is no long version of Range. I'm thinking my only option here is Aggregate but I'm still not very strong with that function.

Finally once I have that query working, I want to return the values from there that are within a time window i.e. between a different start and end date. That is easy enough to do using SkipWhile/TakeWhile.

Here's how I could do it if DateTime.Ticks was an int

from recurrence in Recurrences
let range =
Enumerable
  .Range(
    (int)recurrence.StartDate.Ticks,
    recurrence.EndDate.HasValue ? (int)recurrence.EndDate.Value.Ticks : (int)end.Ticks)
  .Where(i=>i-(int)recurrence.StartDate.Ticks%(int)recurrence.RecurrenceLength.Value==0)
  .SkipWhile(d => d < start.Ticks)
  .TakeWhile(d => d <= end.Ticks)
from date in range
select new ScheduledEvent { Date = new DateTime(date) };

I guess what I need is an implementation of LongRange that could execute over an EF Query.

Upvotes: 1

Views: 2594

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

You could create your own date range method

public static class EnumerableEx
{
    public static IEnumerable<DateTime> DateRange(DateTime startDate, DateTime endDate, TimeSpan intervall)
    {
        for (DateTime d = startDate; d <= endDate; d += intervall) {
            yield return d;
        }
    }
}

Then query with

var query =
    from recurrence in Recurrences
    from date in EnumerableEx.DateRange(recurrence.StartDate,
                                        recurrence.EndDate ?? end,
                                        recurrence.RecurrenceInterval)
    select new ScheduledEvent { Date = date };

This assumes that RecurrenceInterval is declared as TimeSpan and end as DateTime.

EDIT: Would this version restrict the recurrences on the server side as you excpect?

var query =
    from recurrence in Recurrences
    where
        recurrence.StartDate <= end &&
        (recurrence.EndDate != null && recurrence.EndDate.Value >= start ||
         recurrence.EndDate == null)
    from date in EnumerableEx.DateRange(
        recurrence.StartDate,
        recurrence.EndDate.HasValue && recurrence.EndDate.Value < end ? recurrence.EndDate.Value : end,
        recurrence.RecurrenceInterval)
    where (date >= start)
    select new ScheduledEvent { Date = date };

Here the returned recurrences already take in account the start and the end date, thus not returning obsolete recurrences. EnumerableEx.DateRange has no effect on the first part of the query.

Upvotes: 3

CodeGnome
CodeGnome

Reputation: 261

Here's the function that yields the intersection of the Recurrence points and a specified subinterval:

public class Recurrence
{
    public int Id { get; protected set; }
    public DateTime StartDate { get; protected set; }
    public DateTime? EndDate { get; protected set; }
    public long? RecurrenceInterval { get; protected set; }

    // returns the set of DateTimes within [subStart, subEnd] that are
    // of the form StartDate + k*RecurrenceInterval, where k is an Integer
    public IEnumerable<DateTime> GetBetween(DateTime subStart, DateTime subEnd)
    {            
        long stride = RecurrenceInterval ?? 1;
        if (stride < 1) 
            throw new ArgumentException("Need a positive recurrence stride");

        long realStart, realEnd;

        // figure out where we really need to start
        if (StartDate >= subStart)
            realStart = StartDate.Ticks;
        else
        {
            long rem = subStart.Ticks % stride;
            if (rem == 0)
                realStart = subStart.Ticks;
            else
                // break off the incomplete stride and add a full one
                realStart = subStart.Ticks - rem + stride;
        }
        // figure out where we really need to stop
        if (EndDate <= subEnd)
            // we know EndDate has a value. Null can't be "less than" something
            realEnd = EndDate.Value.Ticks; 
        else
        {
            long rem = subEnd.Ticks % stride;
            // break off any incomplete stride
            realEnd = subEnd.Ticks - rem;
        }
        if (realEnd < realStart)
            yield break; // the intersection is empty

        // now yield all the results in the intersection of the sets
        for (long t = realStart; t <= realEnd; t += stride)
            yield return new DateTime(t);
    }

}

Upvotes: 2

Related Questions