I have the following objects in list (toInsertDatesRecords).

class SafeProtectDeviceReportUsage
{
    public long Vehicleid { get; set; }

    public DateTime TimestampLastConnection { get; set; }

    // Minutes
    public double AvgConnectionDuration { get; set; }
}

I need to return the average AvgConnectionDuration for all vehicles on a particular date (dd/MM).

Here is a data sample, many values for AvgConnectedDuration for the same vehicle.

The expected result is

I came with the following query:

var toBeReturned = toInsertDatesRecords.GroupBy(row => row.TimestampLastConnection.ToLocalTime().Date)
    .Select(g =>
    {
        int vCount = g.Select(c => c.Vehicleid).Distinct().Count();
        return new
        {
            date = g.Key.Date,
            count = vCount,
            duration = g.Average(c => c.AvgConnectionDuration)
        };
    });

It is wrong because I need to first sum the AvgConnectionDuration on a particular day for each vehicle before computing the average duration for all vehicles.

I can't figure out how to group by two times.

CodePudding user response：

From your sample data and expect results, you can try to use Sum and divide your count instead of using Average

var result = toInsertDatesRecords.GroupBy(row =>row.TimestampLastConnection.ToLocalTime().Date)
.Select(g => {
    int vCount = g.Select(c => c.Vehicleid).Distinct().Count();
    return new {
      date = g.Key.Date,
      count = vCount,
      AvgConnectedDuration = g.Sum(x=> x.AvgConnectionDuration) / vCount
    };
});

and I think we can use g.GroupBy(x=>x.Vehicleid).Count() count the number make it simple.

var result = toInsertDatesRecords.GroupBy(row =>row.TimestampLastConnection.ToLocalTime().Date)
    .Select(g => {
    int vCount = g.GroupBy(x=>x.Vehicleid).Count();
    return new {
        date = g.Key.Date,
        count = vCount,
        AvgConnectedDuration = g.Sum(x=> x.AvgConnectionDuration) / vCount
    };
});

c# online

CodePudding user response：

Try following :

var toBeReturned = toInsertDatesRecords.GroupBy(row => row.TimestampLastConnection.ToLocalTime().Date)
    .Select(g => new {
            date = g.Key.Date,
            count = g.Select(c => c.Vehicleid).Distinct().Count(),
            duration = g.Average(c => c.AvgConnectionDuration)
        }
    ).select(g => new {date = g.date, count = g.Sum(h => h.count), duration = g,Average(h => h.count));