LINQ to Entities to replace a join to a generated table

Question

I have some SQL that I'm looking to replicate in LINQ to Entities. It has a table with a DateTime column and GPS location at that time for another entity in the system. It CROSS JOINS this table to the output of a table valued function which generates a "time table" over a specific period. The code for this TVF looks something like:

  WHILE @dt_start <= @dt_end
  BEGIN
        INSERT INTO @res (dt) VALUES (@dt_start)
        SET @dt_start = dateadd(ms, @interval_ms, @dt_start)
  END -- WHILE

so then the result from this is cross joined to give a list of GPS locations for the entity at each of the generated times from *dt_start to dt_end*.

My Naive attempt at a simple test to replicate some of this functionality looks something like this:

        var times = new List<DateTime> {DateTime.Parse("2009-04-04"), 
            DateTime.Parse("2009-04-05")}.AsQueryable();
        var query = from gps in Db.GPSDataPointSet
                    from t in times
                    where gps.Driver.Id == 1 && gps.UtcTime <= t
                    group gps by gps.Driver.Id
                    into ggps
                        select ggps.OrderByDescending(gps => gps.Id);
        var test = query.ToList();

But obviously this doesn't work - I include it in the hope it might give you a vague idea of what I'm trying to achieve here. Does anybody have a suggestion for how I might replicate the TVF functionality in a LINQ to Entities query, without it being too inefficient?

Answer 1

If you just want to generate the set of dates from a start time to an end time, try something like this.

public static IEnumerable<DateTime> Intervals(DateTime start,
        DateTime end, int intervalMs)
{
    int number = (int) ((end - start).TotalMilliseconds / intervalMs);
    return Enumerable.Range(0, number).Select(i =>
        start + TimeSpan.FromMilliseconds(intervalMs * i));
}