C
C#•2w ago
C@NYON⭐ONL!NE

✅ LINQ help: IQueryable<Entity> -> Dictionary<string, Entity[]>

Hi, all: I'm looking for some help in writing a LINQ query. I'm interested in creating a dictionary where the keys are LogDatum.SerialNumber (nullable string), and the values are an array of the hourly maximum of LogDatum.Current (nullable double). This means I'm looking for an array whose elements are the maximum LogDatum of a certain hour. A sample of a dictionary I'm looking for: 
Dictionary<string, LogDatum[]> myDict = new() {
    {
        "serial 1",
        [
            <LogDatum with max Current at "2025-01-01 09">,
            <LogDatum with max Current at "2025-01-01 10">,
            <LogDatum with max Current at "2025-01-01 11">,
            <LogDatum with max Current at "2025-01-01 13">,
            <LogDatum with max Current at "2025-01-01 14">,
        ]
    },
};
Dictionary<string, LogDatum[]> myDict = new() {
    {
        "serial 1",
        [
            <LogDatum with max Current at "2025-01-01 09">,
            <LogDatum with max Current at "2025-01-01 10">,
            <LogDatum with max Current at "2025-01-01 11">,
            <LogDatum with max Current at "2025-01-01 13">,
            <LogDatum with max Current at "2025-01-01 14">,
        ]
    },
};
My entity (generated by EF scaffolding) has an abbreviated definition of: 
public class LogDatum
{
    public long? Pk { get; set; }
    public string? SerialNumber { get; set; }
    public DateTime? Timestamp { get; set; }
    public double? Current { get; set; }
}
public class LogDatum
{
    public long? Pk { get; set; }
    public string? SerialNumber { get; set; }
    public DateTime? Timestamp { get; set; }
    public double? Current { get; set; }
}
I have a query that starts to get me there: 
var res = await _nktContext.LogData
    .Where(data => data.Timestamp != null && data.Timestamp.Value >= dateStart && data.Timestamp.Value <= dateEnd)
    .GroupBy(data => new { data.SerialNumber, Hour = data.Timestamp!.Value.ToString("yyyy-MM-dd HH") })
    .ToDictionaryAsync(
        grouping => grouping.Key.SerialNumber ?? "N/A",
        grouping => grouping.ToArray()
    );
var res = await _nktContext.LogData
    .Where(data => data.Timestamp != null && data.Timestamp.Value >= dateStart && data.Timestamp.Value <= dateEnd)
    .GroupBy(data => new { data.SerialNumber, Hour = data.Timestamp!.Value.ToString("yyyy-MM-dd HH") })
    .ToDictionaryAsync(
        grouping => grouping.Key.SerialNumber ?? "N/A",
        grouping => grouping.ToArray()
    );
However this gets me all the grouped by an hour, which of course is caused by grouping => grouping.ToArray(), but I'm not clever enough to get the rest of the way there. Any help or tips appreciated, thanks!
2 Replies
Sehra
Sehra•2w ago
add .GroupBy(data => data.Key.SerialNumber, data => data.MaxBy(ld => ld.Current)) before ToDictionary
C@NYON⭐ONL!NE
C@NYON⭐ONL!NEOP•2w ago
Great ty! I had to change my query a little bit because I think my DB connector (Snowflake) is shoddy: 
var logs = await _nktContext.LogData
    .Where(log => log.Timestamp != null &&
        log.Timestamp.Value >= dateStart &&
        log.Timestamp.Value <= dateEnd &&
        !string.IsNullOrEmpty(log.SerialNumber) &&
        log.LddCurrent != null)
    .ToListAsync();

var filtered = logs
    .GroupBy(log => new
    {
        log.SerialNumber,
        Hour = new DateTime( // slightly faster than ToString
            log.Timestamp!.Value.Year,
            log.Timestamp!.Value.Month,
            log.Timestamp!.Value.Day,
            log.Timestamp!.Value.Hour,
            1,
            1
        ),
    })
    .GroupBy(
        group => group.Key.SerialNumber!,
        group => group.MaxBy(log => log.LddCurrent)!
    )
    .ToDictionary(
        group => group.Key,
        group => group.OrderBy(log => log!.Timestamp).ToArray()
    );
var logs = await _nktContext.LogData
    .Where(log => log.Timestamp != null &&
        log.Timestamp.Value >= dateStart &&
        log.Timestamp.Value <= dateEnd &&
        !string.IsNullOrEmpty(log.SerialNumber) &&
        log.LddCurrent != null)
    .ToListAsync();

var filtered = logs
    .GroupBy(log => new
    {
        log.SerialNumber,
        Hour = new DateTime( // slightly faster than ToString
            log.Timestamp!.Value.Year,
            log.Timestamp!.Value.Month,
            log.Timestamp!.Value.Day,
            log.Timestamp!.Value.Hour,
            1,
            1
        ),
    })
    .GroupBy(
        group => group.Key.SerialNumber!,
        group => group.MaxBy(log => log.LddCurrent)!
    )
    .ToDictionary(
        group => group.Key,
        group => group.OrderBy(log => log!.Timestamp).ToArray()
    );
That's another issue though 🥴

Did you find this page helpful?