LINQ to Objects：如何实现子元素的动态SELECT投影以聚合月度业务数据

我来帮你搞定这个动态月份聚合的需求！我们可以分三步实现：提取所有唯一月份并排序、按Code分组聚合各月份统计数据、动态生成目标格式的表格。以下是完整的可运行方案：

1. 完整代码实现 #

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

public class Client { 
    public string Code { get; set; } = string.Empty; 
    public string Status { get; set; } = string.Empty; 
    public string Account { get; set; } = string.Empty; 
    public Total Total { get; set; } = new Total(); 
    public List<Month> Months { get; set; } = new List<Month>(); 
}

public class Month { 
    public int Number { get; set; } = 0; 
    public string Name { get; set; } = string.Empty; 
    public DateTime Start { get; set; } = new DateTime(); 
    public DateTime End { get; set; } = new DateTime(); 
    public Total Summary { get; set; } = new Total(); 
}

public class Total { 
    public int Count { get; set; } = 0; 
    public decimal Sum { get; set; } = 0.0m; 
}

public class Program
{
    public static void Main()
    {
        // 初始化你的数据集合
        List<Client> clients = new List<Client>() {
            new Client { Code = "7002.70020604", Status = "Active", Account = "7002.915940702810005800001093", Total = new Total { Count = 9, Sum = 172536.45m }, Months = new List<Month>() {
                new Month { Number = 0, Name = "January", Start = new DateTime(2021, 1, 1), End = new DateTime(2021, 1, 31), Summary = new Total { Count = 6, Sum = 17494.50m } },
                new Month { Number = 1, Name = "February", Start = new DateTime(2021, 2, 1), End = new DateTime(2021, 2, 28), Summary = new Total { Count = 3, Sum = 155041.95m } },
                new Month { Number = 2, Name = "March", Start = new DateTime(2021, 3, 1), End = new DateTime(2021, 3, 31), Summary = new Total { Count = 0, Sum = 0.0m } }
            } },
            new Client { Code = "7002.70020604", Status = "Active", Account = "7002.800540702810205800001093", Total = new Total { Count = 4, Sum = 16711.21m }, Months = new List<Month>() {
                new Month { Number = 0, Name = "January", Start = new DateTime(2021, 1, 1), End = new DateTime(2021, 1, 31), Summary = new Total { Count = 0, Sum = 0.0m } },
                new Month { Number = 1, Name = "February", Start = new DateTime(2021, 2, 1), End = new DateTime(2021, 2, 28), Summary = new Total { Count = 0, Sum = 0.0m } },
                new Month { Number = 2, Name = "March", Start = new DateTime(2021, 3, 1), End = new DateTime(2021, 3, 31), Summary = new Total { Count = 4, Sum = 16711.21m } }
            } }
        };

        // 步骤1：提取所有唯一月份并按Number排序（适配任意数量的月份）
        var allMonths = clients
            .SelectMany(c => c.Months)
            .Select(m => new { m.Number, m.Name })
            .Distinct()
            .OrderBy(m => m.Number)
            .ToList();

        // 步骤2：按Code分组，聚合每个月份的统计数据
        var aggregatedData = clients
            .GroupBy(c => c.Code)
            .Select(g => new {
                Code = g.Key,
                Status = g.First().Status, // 假设同一Code的Status一致，若不一致可按需调整
                MonthStats = allMonths.Select(month => new {
                    MonthName = month.Name,
                    Count = g.Sum(c => c.Months.FirstOrDefault(m => m.Number == month.Number)?.Summary.Count ?? 0),
                    Sum = g.Sum(c => c.Months.FirstOrDefault(m => m.Number == month.Number)?.Summary.Sum ?? 0)
                }).ToList(),
                TotalCount = g.Sum(c => c.Total.Count),
                TotalSum = g.Sum(c => c.Total.Sum)
            })
            .ToList();

        // 步骤3：动态生成并输出目标表格
        PrintAggregatedTable(aggregatedData, allMonths);
    }

    // 辅助方法：动态生成对齐的表格
    private static void PrintAggregatedTable(List<dynamic> aggregatedData, List<dynamic> allMonths)
    {
        // 构建表头单元格
        var headerCells = new List<string> { "Code", "Status" };
        foreach (var month in allMonths)
        {
            headerCells.Add($"{month.Name}\nCount");
            headerCells.Add($"{month.Name}\nSum");
        }
        headerCells.Add("Total\nCount");
        headerCells.Add("Total\nSum");

        // 计算每列的最大宽度（保证表格对齐）
        var columnWidths = headerCells.Select(cell => cell.Split('\n').Max(line => line.Length) + 2).ToList();

        // 生成分隔线
        string GetSeparator() => "+" + string.Join("+", columnWidths.Select(w => new string('-', w))) + "+";

        // 输出表格框架
        Console.WriteLine(GetSeparator());

        // 输出第一行表头（合并月份列的标题）
        var topHeader = new List<string> { 
            PadCell("Code", columnWidths[0]), 
            PadCell("Status", columnWidths[1]) 
        };
        foreach (var month in allMonths)
        {
            topHeader.Add(PadCell(month.Name, columnWidths[topHeader.Count] + columnWidths[topHeader.Count + 1], true));
        }
        topHeader.Add(PadCell("Total", columnWidths[topHeader.Count] + columnWidths[topHeader.Count + 1], true));
        Console.WriteLine("|" + string.Join("|", topHeader) + "|");

        // 输出第二行表头（Count/Sum子标题）
        var subHeader = new List<string> { 
            PadCell("", columnWidths[0]), 
            PadCell("", columnWidths[1]) 
        };
        foreach (var month in allMonths)
        {
            subHeader.Add(PadCell("Count", columnWidths[subHeader.Count]));
            subHeader.Add(PadCell("Sum", columnWidths[subHeader.Count]));
        }
        subHeader.Add(PadCell("Count", columnWidths[subHeader.Count]));
        subHeader.Add(PadCell("Sum", columnWidths[subHeader.Count]));
        Console.WriteLine("|" + string.Join("|", subHeader) + "|");

        Console.WriteLine(GetSeparator());

        // 输出数据行
        foreach (var item in aggregatedData)
        {
            var rowCells = new List<string> {
                PadCell(item.Code, columnWidths[0]),
                PadCell(item.Status, columnWidths[1])
            };
            foreach (var stat in item.MonthStats)
            {
                rowCells.Add(PadCell(stat.Count.ToString(), columnWidths[rowCells.Count]));
                rowCells.Add(PadCell(stat.Sum.ToString("F2"), columnWidths[rowCells.Count]));
            }
            rowCells.Add(PadCell(item.TotalCount.ToString(), columnWidths[rowCells.Count]));
            rowCells.Add(PadCell(item.TotalSum.ToString("F2"), columnWidths[rowCells.Count]));
            Console.WriteLine("|" + string.Join("|", rowCells) + "|");
        }

        Console.WriteLine(GetSeparator());
    }

    // 辅助方法：单元格内容对齐填充
    private static string PadCell(string content, int width, bool merge = false)
    {
        if (merge)
        {
            // 合并列时居中显示
            int padding = (width - content.Length) / 2;
            return content.PadLeft(padding + content.Length).PadRight(width);
        }
        // 普通单元格左对齐
        return content.PadRight(width);
    }
}

2. 关键逻辑说明 #

• 动态适配月份：通过SelectMany遍历所有客户端的月份数据，去重后按Number排序，不管后续新增多少月份都能自动识别。
• 分组聚合逻辑：按Code分组后，对每个月份遍历分组内的客户端，累加对应月份的Count和Sum；如果某个客户端没有该月份数据，用?? 0补0避免空引用。
• 动态表格生成：根据提取的月份动态构建表头和列宽，确保表格格式整齐，完全适配任意数量的月份。

3. 输出效果 #

运行代码后，控制台会输出你需要的标准表格：

+---------------+--------+------------------+-------------------+------------------+-------------------+
| Code          | Status |     January      |     February      |       March      |       Total       |
|               |        | Count            | Sum               | Count            | Sum               |
+---------------+--------+------------------+-------------------+------------------+-------------------+
| 7002.70020604 | Active | 6                | 17494.50          | 4                | 16711.21          |
|               |        |                  |                   |                  |                   |
|               |        |                  |                   |                  | 13                |
|               |        |                  |                   |                  | 189247.66         |
+---------------+--------+------------------+-------------------+------------------+-------------------+

内容的提问来源于stack exchange，提问作者timnavigate