GroupJoin by date where date in second list between two dates from first

Question

I have two tables:

First is TimeValues (Example)

time     | value
12/28/18 | 5.6
01/03/19 | 5.6
01/04/19 | 5.6
01/09/19 | 5.6
01/15/19 | 5.6
02/03/19 | 5.6

Second is LogicalPeriods

DateFrom | DateTo
12/16/18 | 12/23/18
12/23/18 | 12/30/18
12/30/18 | 01/06/19
01/06/19 | 01/13/19
01/13/19 | 01/20/19
01/20/19 | 01/27/19
01/27/19 | 02/03/19
02/03/19 | 02/10/19

What am I need to groupjoin using Linq the TimeValues table with the LogicalPeriods table (time must be > DateFrom and <= DateTo) and the result table will have two columns DateTo and SUM(value), if there are no any timevalues between period, just return null. From my example

DateTo   | value
12/23/18 | null
12/30/18 | 5.6
01/06/19 | 11.2
01/13/19 | 5.6
01/20/19 | 5.6
01/27/19 | null
02/03/19 | 5.6
02/10/19 | null

Here is an example of what I have so far

var TimeValues = new List<TimeValue>
{
    new TimeValue {time = DateTime.Parse("12/28/18"), value = 5.6 },
    new TimeValue {time = DateTime.Parse("01/03/19"), value = 5.6 },
    new TimeValue {time = DateTime.Parse("01/04/19"), value = 5.6 },
    new TimeValue {time = DateTime.Parse("01/09/19"), value = 5.6 },
    new TimeValue {time = DateTime.Parse("01/15/19"), value = 5.6 },
    new TimeValue {time = DateTime.Parse("02/03/19"), value = 5.6 },
};

var LogicalPeriods = new List<LogicalPeriod>
{
    new LogicalPeriod { DateFrom = DateTime.Parse("12/28/18"), DateTo = DateTime.Parse("12/23/18") },
    new LogicalPeriod { DateFrom = DateTime.Parse("12/23/18"), DateTo = DateTime.Parse("12/30/18") },
    new LogicalPeriod { DateFrom = DateTime.Parse("12/30/18"), DateTo = DateTime.Parse("01/06/19") },
    new LogicalPeriod { DateFrom = DateTime.Parse("01/06/19"), DateTo = DateTime.Parse("01/13/19") },
    new LogicalPeriod { DateFrom = DateTime.Parse("01/13/19"), DateTo = DateTime.Parse("01/20/19") },
    new LogicalPeriod { DateFrom = DateTime.Parse("01/20/19"), DateTo = DateTime.Parse("01/27/19") },
    new LogicalPeriod { DateFrom = DateTime.Parse("01/27/19"), DateTo = DateTime.Parse("02/03/19") },
    new LogicalPeriod { DateFrom = DateTime.Parse("02/03/19"), DateTo = DateTime.Parse("02/10/19") },
};

var result = LogicalPeriods.GroupJoin(
    TimeValues,
    period => new { period.DateFrom, period.DateTo },
    tv => tv.time,
    (period, tv) => new {period.DateTo, timeValues = tv })// I don't know what should I need do here

I found that GroupJoin uses only equality comparer, but I need the between comparison. Will be helpful to suggest the solution using Method syntax.

Answer 1

Using the following logic I was able to get the expected results shown above

CultureInfo provider = CultureInfo.InvariantCulture;
var timeValues = new List<TimeValue>
{
    new TimeValue {time = DateTime.ParseExact("12/28/18","MM/dd/yy",provider), value = 5.6 },
    new TimeValue {time = DateTime.ParseExact("01/03/19","MM/dd/yy",provider), value = 5.6 },
    new TimeValue {time = DateTime.ParseExact("01/04/19","MM/dd/yy",provider), value = 5.6 },
    new TimeValue {time = DateTime.ParseExact("01/09/19","MM/dd/yy",provider), value = 5.6 },
    new TimeValue {time = DateTime.ParseExact("01/15/19","MM/dd/yy",provider), value = 5.6 },
    new TimeValue {time = DateTime.ParseExact("02/03/19","MM/dd/yy",provider), value = 5.6 },
};

var logicalPeriods = new List<LogicalPeriod>
{
    new LogicalPeriod { DateFrom = DateTime.ParseExact("12/28/18","MM/dd/yy",provider), DateTo = DateTime.ParseExact("12/23/18","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("12/23/18","MM/dd/yy",provider), DateTo = DateTime.ParseExact("12/30/18","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("12/30/18","MM/dd/yy",provider), DateTo = DateTime.ParseExact("01/06/19","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("01/06/19","MM/dd/yy",provider), DateTo = DateTime.ParseExact("01/13/19","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("01/13/19","MM/dd/yy",provider), DateTo = DateTime.ParseExact("01/20/19","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("01/20/19","MM/dd/yy",provider), DateTo = DateTime.ParseExact("01/27/19","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("01/27/19","MM/dd/yy",provider), DateTo = DateTime.ParseExact("02/03/19","MM/dd/yy",provider) },
    new LogicalPeriod { DateFrom = DateTime.ParseExact("02/03/19","MM/dd/yy",provider), DateTo = DateTime.ParseExact("02/10/19","MM/dd/yy",provider) },
};


var result = logicalPeriods.GroupJoin(timeValues,
    p => p,
    t => logicalPeriods.FirstOrDefault(l => t.time > l.DateFrom && t.time <= l.DateTo),
    (p, times) => new {
        p.DateTo,
        value = times.Count() > 0 ? (double?)times.Sum(t => t.value) : null
    }
);

Yielding the expected results

DateTo   | value
12/23/18 | null
12/30/18 | 5.6
01/06/19 | 11.2
01/13/19 | 5.6
01/20/19 | 5.6
01/27/19 | null
02/03/19 | 5.6
02/10/19 | null

However, as mentioned in the comments by @IvanStoev

The truth (hence the answer) is that there is no good standard LINQ solution for this type of problem (there are efficient non LINQ algorithmic solutions though).

With that I was able to achieve basically the same result using

var result = logicalPeriods.Select(p => new 
{ 
    p.DateTo, 
    value = timeValues.Where(t => t.time > p.DateFrom && t.time <= p.DateTo).Sum(t => t.value) 
}).ToList();

Which, while producing similar result to the original solution, both are not very efficient when dealing with large result sets.