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

Question

我有两张桌子：

首先是TimeValues（示例）

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

其次是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

我需要使用 Linq 将TimeValues表与LogicalPeriods表（时间必须是 > DateFrom和 <= DateTo）进行分组，结果表将有两列DateTo和SUM(value)，如果期间之间没有任何时间值，只返回null。从我的例子

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

这是我到目前为止的一个例子

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

我发现GroupJoin只使用相等比较器，但我需要比较。使用方法语法建议解决方案将很有帮助。

标签： c#.netlinq

Answer 1

使用以下逻辑，我能够得到上面显示的预期结果

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
    }
);

产生预期的结果

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

然而，正如@IvanStoev 在评论中提到的那样

事实（因此得到答案）是对于这类问题没有好的标准 LINQ 解决方案（尽管有有效的非 LINQ 算法解决方案）。

有了这个，我能够使用基本相同的结果

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();

虽然产生与原始解决方案相似的结果，但在处理大型结果集时两者都不是很有效。