How can I group by aggregation?

Question

I have the following data:

ATMID    StatusId  ComponentId   FromDateTime          ToDateTime
4           1          3        2019-01-01 18:20     2019-01-01 18:45
4           2          5        2019-01-01 18:25     2019-01-01 18:45
5           3          2        2019-01-01 18:10     2019-01-01 18:45 
5           1          2        2019-01-01 18:00     2019-01-01 18:45
5           6          5        2019-01-01 18:46     2019-01-01 18:50

I would like to aggregate this in such a way that the result is:

Atmid    St_Comp         FromDateTime          ToDateTime
  4    {(1,3),(2,5)}    2019-01-01 18:20      2019-01-01 18:45 
  5    {(3,2),(1,2)}    2019-01-01 18:00      2019-01-01 18:45 
  5    {(6,5)}          2019-01-01 18:46      2019-01-01 18:50 

How can I achieve this using Linq?

Here is my code:

var grouped = Problem_Fact_Measure_Overalls.GroupBy(
                  i => new { i.ATMId},
                  i => new {i.ComponentId,i.StatusId},
                  (Atmid, St_Comp) => new { Atmid, St_Comp });

but I am unable to aggregate on Min of FromDateTime.

Answer 1

Need to use Aggregate method like this:

var result = data
    .Aggregate(
        new List<KeyValuePair<DateTimeRange, List<AtmRecord>>>(),
        (accumulator, atmRecord) => {
            var segment = accumulator.FirstOrDefault(
                v => atmRecord.ATMID == v.Value.First().ATMID && 
                    atmRecord.FromDateTime <= v.Key.To &&
                    atmRecord.ToDateTime >= v.Key.From);

            if (segment.Key == null)
            {
                accumulator.Add(new KeyValuePair<DateTimeRange, List<AtmRecord>>(
                    new DateTimeRange(atmRecord.FromDateTime, atmRecord.ToDateTime),
                    new List<AtmRecord>() {atmRecord}));

                return accumulator; 
            }

            if (atmRecord.FromDateTime < segment.Key.From)
            {
                segment.Key.From = atmRecord.FromDateTime;
            }

            if (atmRecord.ToDateTime > segment.Key.To)
            {
                segment.Key.To = atmRecord.ToDateTime;
            }                        

            segment.Value.Add(atmRecord);

            return accumulator;
        },
        accumulator => {
            return accumulator
                .Select(v => new 
                {
                    Atmid = v.Value.First().ATMID,
                    St_Comp = v.Value
                        .Select(r => (r.StatusId, r.ComponentId))
                        .ToArray(),
                    FromDateTime = v.Key.From,
                    ToDateTime = v.Key.To
                });
        })
        .ToArray();

/* result: 
[0]:{ Atmid = 4, St_Comp = {(int, int)[2]}, FromDateTime = {2019-01-01 6:20:00 p.m.}, ToDateTime = {2019-01-01 6:45:00 p.m.} }
Atmid [int]:4
FromDateTime [DateTime]:{2019-01-01 6:20:00 p.m.}
St_Comp:{(int, int)[2]}
[0]:(1, 3)
[1]:(2, 5)
ToDateTime [DateTime]:{2019-01-01 6:45:00 p.m.}

[1]:{ Atmid = 5, St_Comp = {(int, int)[2]}, FromDateTime = {2019-01-01 6:00:00 p.m.}, ToDateTime = {2019-01-01 6:45:00 p.m.} }
Atmid [int]:5
FromDateTime [DateTime]:{2019-01-01 6:00:00 p.m.}
St_Comp:{(int, int)[2]}
[0]:(3, 2)
[1]:(1, 2)
ToDateTime [DateTime]:{2019-01-01 6:45:00 p.m.}

[2]:{ Atmid = 5, St_Comp = {(int, int)[1]}, FromDateTime = {2019-01-01 6:46:00 p.m.}, ToDateTime = {2019-01-01 6:50:00 p.m.} }
Atmid [int]:5
FromDateTime [DateTime]:{2019-01-01 6:46:00 p.m.}
St_Comp:{(int, int)[1]}
[0]:(6, 5)
ToDateTime [DateTime]:{2019-01-01 6:50:00 p.m.}
 */

The test data:

var data = new[]{ 
    new AtmRecord( 4, 1, 3, DateTime.Parse("2019-01-01 18:20"), DateTime.Parse("2019-01-01 18:45") ),
    new AtmRecord( 4, 2, 5, DateTime.Parse("2019-01-01 18:25"), DateTime.Parse("2019-01-01 18:45") ),
    new AtmRecord( 5, 3, 2, DateTime.Parse("2019-01-01 18:10"), DateTime.Parse("2019-01-01 18:45") ),
    new AtmRecord( 5, 1, 2, DateTime.Parse("2019-01-01 18:00"), DateTime.Parse("2019-01-01 18:45") ),
    new AtmRecord( 5, 6, 5, DateTime.Parse("2019-01-01 18:46"), DateTime.Parse("2019-01-01 18:50") )
};

The additional classes:

internal sealed class AtmRecord
{
    public int ATMID { get; }
    public int StatusId { get; }
    public int ComponentId { get; }
    public DateTime FromDateTime { get; }
    public DateTime ToDateTime { get; }

    public AtmRecord(int aTMID, int statusId, int componentId, DateTime fromDateTime, DateTime toDateTime)
    {
        ATMID = aTMID;
        StatusId = statusId;
        ComponentId = componentId;
        FromDateTime = fromDateTime;
        ToDateTime = toDateTime;
    }
}

internal sealed class DateTimeRange
{
    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public DateTimeRange(DateTime from, DateTime to)
    {
        From = from;
        To = to;
    }
}