c# - Linq - 如何按 'let' 值分组并求和另一个 'let' 值

Question

我正在使用一个我无法控制的 API，它以 List<string> 的形式从 SQL 服务器返回数据每个字符串都有以逗号分隔的列。

此特定数据集作为 varchar 返回和 datetime像这样:

{
    "Prefix_IdentifierA,1/1/1900 12:30:00 AM,", 
    "Prefix_IdentifierA,1/1/1900 12:15:00 AM,", 
    "Prefix_IdentifierB,1/1/1900 01:00:00 AM,", 
    "Prefix_IdentifierB,1/1/1900 12:45:00 AM,"
}

datetime仅代表一个时间，因此所有日期都是相同的。

我需要将其转换为对象集合，每个标识符具有总持续时间:

public class Log
{
    public string Identifier { get; set; }

    public int DurationInSeconds { get; set; }
}

因此生成的集合应该如下所示:

{
    Log { Identifier = "IdentifierA", DurationInSeconds = 45 },
    Log { Identifier = "IdentifierB", DurationInSeconds = 105 }
}

目前我在 2 个 Linq 查询中这样做:

1) 提取 Identifier和 DurationInSeconds从每一行:

var logs1 = (from row in rows
             select row.Split(',')
             into columns
             let identifier = columns[0].Replace("Prefix_", string.Empty)
             let duration = DateTime.Parse(columns[1])
             let durationInSeconds = duration.Hour * 3600 + duration.Minute * 60 + duration.Second
             select new { Identifier = identifier, DurationInSeconds = durationInSeconds }).ToList();

2) 按 Identifier 分组求和DurationInSeconds :

var logs2 = (from log in logs1
             group log by log.Identifier
             into grouping
             orderby grouping.Key
             select new Log { Identifier = grouping.Key, DurationInSeconds = grouping.Sum(log => log.DurationInSeconds) }).ToList();

第一个查询的匿名类型与 Log 相同对象，那么有什么办法可以将这两个查询合并为一个吗？

编辑:我目前的进度

我已经按 identifier 分组了但不知道如何得到 durationInSeconds 的总和:

var logs = (from row in rows
            select row.Split(',')
            into columns
            let identifier = columns[0].Replace("Prefix_", string.Empty)
            let duration = DateTime.Parse(columns[1])
            let durationInSeconds = duration.Hour * 3600 + duration.Minute * 60 + duration.Second
            group rows by identifier
            into grouping
            select new { Identifier = grouping.Key, DurationInSeconds = grouping.Sum(?????) }).ToList();

Answer 1

您可以按 identifier 分组返回 IGrouping<IEnumarable<T>> , 所以你可以从中获取标识符，它只是 Key , 接下来你可以再次投影 IEnumarable为每个标识符呈现并解析 duration变量本身。请注意，在对数据进行分组之前无需存储它。

这个查询应该适合你:-

var logs =  (from row in rows
            select row.Split(',') into columns
            let identifier = columns[0].Replace("Prefix_", string.Empty)
            group columns by identifier into g
            select new Log { 
                  Identifier = g.Key, 
                  DurationInSeconds = (from dur in g
                                      let duration = DateTime.Parse(dur[1])
                                      select duration.Hour * 3600 + duration.Minute * 60 
                                              + duration.Second).Sum()
                           }).ToList();