基于JSON配置的DataTable动态聚合查询问题（VB.NET/C#）

云上老周

2026-7-21

基于JSON配置的DataTable动态聚合方案（修正Dynamic LINQ问题）

问题说明

现有基于Dynamic LINQ的DataTable动态聚合方案存在两个核心问题：

分组列非首列时结果映射异常
结果表未过滤无关列，保留了原表中无需聚合的字段

以下提供修正后的Dynamic LINQ方案，以及非LINQ的手动分组方案，均支持基于JSON配置的动态聚合。

1. 定义JSON配置模型

首先创建对应JSON结构的实体类，用于解析配置：

public class AggregationConfig
{
    public List<string> GroupByColumns { get; set; }
    public List<AggregationColumn> AggregationColumns { get; set; }
}

public class AggregationColumn
{
    public string ColumnName { get; set; }
    public string AggregationType { get; set; } // 支持Sum/Avg/Count/Max/Min/CountDistinct
}

2. 修正后的Dynamic LINQ方案

依赖System.Linq.Dynamic.Core NuGet包，解决分组列顺序问题和无关列过滤问题：

using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Linq.Dynamic.Core;

public static class DataTableDynamicAggregator
{
    public static DataTable Aggregate(DataTable sourceTable, string jsonConfig)
    {
        // 解析配置
        var config = JsonConvert.DeserializeObject<AggregationConfig>(jsonConfig);

        // 参数校验
        if (config.GroupByColumns?.Any() != true)
            throw new ArgumentException("必须至少指定一个分组列");
        if (config.AggregationColumns?.Any() != true)
            throw new ArgumentException("必须至少指定一个聚合列");

        // 构造分组键表达式：明确指定分组列，不受原表列顺序影响
        var groupByExpr = $"new ({string.Join(", ", config.GroupByColumns)})";

        // 构造选择表达式：仅保留分组列和聚合列
        var selectItems = new List<string>();
        // 添加分组列（通过Key引用分组键属性）
        selectItems.AddRange(config.GroupByColumns.Select(col => $"Key.{col} as {col}"));
        // 添加聚合列
        foreach (var aggCol in config.AggregationColumns)
        {
            string aggExpr = aggCol.AggregationType switch
            {
                "CountDistinct" => $"{aggCol.ColumnName}.Distinct().Count() as CountDistinct_{aggCol.ColumnName}",
                _ => $"{aggCol.AggregationType}({aggCol.ColumnName}) as {aggCol.AggregationType}_{aggCol.ColumnName}"
            };
            selectItems.Add(aggExpr);
        }
        var selectExpr = $"new ({string.Join(", ", selectItems)})";

        // 执行动态查询
        var query = sourceTable.AsEnumerable()
            .GroupBy(groupByExpr, "it")
            .Select(selectExpr);

        // 构建结果DataTable
        var resultTable = new DataTable();
        // 添加分组列
        foreach (var col in config.GroupByColumns)
        {
            resultTable.Columns.Add(col, sourceTable.Columns[col].DataType);
        }
        // 添加聚合列
        foreach (var aggCol in config.AggregationColumns)
        {
            string colName = $"{aggCol.AggregationType}_{aggCol.ColumnName}";
            Type aggType = aggCol.AggregationType switch
            {
                "Sum" => sourceTable.Columns[aggCol.ColumnName].DataType,
                "Avg" => typeof(double),
                "Count" => typeof(int),
                "Max" => sourceTable.Columns[aggCol.ColumnName].DataType,
                "Min" => sourceTable.Columns[aggCol.ColumnName].DataType,
                "CountDistinct" => typeof(int),
                _ => typeof(object)
            };
            resultTable.Columns.Add(colName, aggType);
        }

        // 填充结果数据
        foreach (var item in query)
        {
            var row = resultTable.NewRow();
            foreach (var prop in item.GetType().GetProperties())
            {
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            }
            resultTable.Rows.Add(row);
        }

        return resultTable;
    }
}

关键修正点

分组列非首列问题解决：通过new (Col1, Col2)明确构造分组键，直接引用列名而非依赖原表列顺序；Select阶段通过Key.ColName精准映射分组值。
无关列过滤：结果表仅包含配置中定义的分组列和聚合列，Select语句仅选择需要的字段，避免无关列混入。

3. 非LINQ手动分组方案

若不想依赖Dynamic LINQ，可采用字典手动分组的方式实现：

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;

public static class DataTableManualAggregator
{
    public static DataTable Aggregate(DataTable sourceTable, string jsonConfig)
    {
        var config = JsonConvert.DeserializeObject<AggregationConfig>(jsonConfig);
        var groupByCols = config.GroupByColumns;
        var aggCols = config.AggregationColumns;

        // 存储分组数据：分组键 -> {分组列值, 聚合累加器}
        var groupDict = new Dictionary<string, Dictionary<string, object>>();

        // 遍历原表行，构建分组并更新聚合值
        foreach (DataRow row in sourceTable.Rows)
        {
            // 生成唯一分组键（处理null值）
            var keyParts = groupByCols.Select(col => row[col]?.ToString() ?? "NULL");
            string groupKey = string.Join("|", keyParts);

            if (!groupDict.ContainsKey(groupKey))
            {
                // 初始化分组数据
                var groupData = new Dictionary<string, object>();
                // 保存分组列值
                foreach (var col in groupByCols)
                {
                    groupData[col] = row[col];
                }
                // 初始化聚合累加器
                foreach (var agg in aggCols)
                {
                    switch (agg.AggregationType)
                    {
                        case "Sum":
                        case "Avg":
                            groupData[$"Sum_{agg.ColumnName}"] = 0m;
                            groupData[$"Count_{agg.ColumnName}"] = 0;
                            break;
                        case "Count":
                            groupData[$"Count_{agg.ColumnName}"] = 0;
                            break;
                        case "Max":
                        case "Min":
                            groupData[$"{agg.AggregationType}_{agg.ColumnName}"] = row[agg.ColumnName];
                            break;
                        case "CountDistinct":
                            groupData[$"Distinct_{agg.ColumnName}"] = new HashSet<object>();
                            break;
                    }
                }
                groupDict[groupKey] = groupData;
            }

            // 更新聚合值
            var groupData = groupDict[groupKey];
            foreach (var agg in aggCols)
            {
                object value = row[agg.ColumnName];
                if (value == DBNull.Value) continue;

                switch (agg.AggregationType)
                {
                    case "Sum":
                        groupData[$"Sum_{agg.ColumnName}"] = Convert.ToDecimal(groupData[$"Sum_{agg.ColumnName}"]) + Convert.ToDecimal(value);
                        break;
                    case "Avg":
                        groupData[$"Sum_{agg.ColumnName}"] = Convert.ToDecimal(groupData[$"Sum_{agg.ColumnName}"]) + Convert.ToDecimal(value);
                        groupData[$"Count_{agg.ColumnName}"] = Convert.ToInt32(groupData[$"Count_{agg.ColumnName}"]) + 1;
                        break;
                    case "Count":
                        groupData[$"Count_{agg.ColumnName}"] = Convert.ToInt32(groupData[$"Count_{agg.ColumnName}"]) + 1;
                        break;
                    case "Max":
                        var currentMax = Convert.ToDecimal(groupData[$"Max_{agg.ColumnName}"]);
                        var newValue = Convert.ToDecimal(value);
                        if (newValue > currentMax) groupData[$"Max_{agg.ColumnName}"] = newValue;
                        break;
                    case "Min":
                        var currentMin = Convert.ToDecimal(groupData[$"Min_{agg.ColumnName}"]);
                        var newMinValue = Convert.ToDecimal(value);
                        if (newMinValue < currentMin) groupData[$"Min_{agg.ColumnName}"] = newMinValue;
                        break;
                    case "CountDistinct":
                        var distinctSet = (HashSet<object>)groupData[$"Distinct_{agg.ColumnName}"];
                        distinctSet.Add(value);
                        break;
                }
            }
        }

        // 构建结果表
        var resultTable = new DataTable();
        // 添加分组列
        foreach (var col in groupByCols)
        {
            resultTable.Columns.Add(col, sourceTable.Columns[col].DataType);
        }
        // 添加聚合列
        foreach (var agg in aggCols)
        {
            string colName = $"{agg.AggregationType}_{agg.ColumnName}";
            Type colType = agg.AggregationType switch
            {
                "Sum" => sourceTable.Columns[agg.ColumnName].DataType,
                "Avg" => typeof(double),
                "Count" => typeof(int),
                "Max" => sourceTable.Columns[agg.ColumnName].DataType,
                "Min" => sourceTable.Columns[agg.ColumnName].DataType,
                "CountDistinct" => typeof(int),
                _ => typeof(object)
            };
            resultTable.Columns.Add(colName, colType);
        }

        // 填充结果行
        foreach (var kvp in groupDict)
        {
            var row = resultTable.NewRow();
            var groupData = kvp.Value;

            // 填充分组列
            foreach (var col in groupByCols)
            {
                row[col] = groupData[col] ?? DBNull.Value;
            }

            // 填充聚合列
            foreach (var agg in aggCols)
            {
                string colName = $"{agg.AggregationType}_{agg.ColumnName}";
                object value = null;

                switch (agg.AggregationType)
                {
                    case "Sum":
                        value = groupData[$"Sum_{agg.ColumnName}"];
                        break;
                    case "Avg":
                        var sum = Convert.ToDecimal(groupData[$"Sum_{agg.ColumnName}"]);
                        var count = Convert.ToInt32(groupData[$"Count_{agg.ColumnName}"]);
                        value = count > 0 ? (double)(sum / count) : DBNull.Value;
                        break;
                    case "Count":
                        value = groupData[$"Count_{agg.ColumnName}"];
                        break;
                    case "Max":
                    case "Min":
                        value = groupData[$"{agg.AggregationType}_{agg.ColumnName}"];
                        break;
                    case "CountDistinct":
                        var distinctSet = (HashSet<object>)groupData[$"Distinct_{agg.ColumnName}"];
                        value = distinctSet.Count;
                        break;
                }

                row[colName] = value ?? DBNull.Value;
            }

            resultTable.Rows.Add(row);
        }

        return resultTable;
    }
}

4. 使用示例

// 示例JSON配置
string configJson = @"{
    ""GroupByColumns"": [""Category"", ""Region""],
    ""AggregationColumns"": [
        { ""ColumnName"": ""Amount"", ""AggregationType"": ""Sum"" },
        { ""ColumnName"": ""Quantity"", ""AggregationType"": ""Avg"" },
        { ""ColumnName"": ""OrderId"", ""AggregationType"": ""CountDistinct"" }
    ]
}";

// 假设sourceTable为你的原始DataTable
DataTable result = DataTableDynamicAggregator.Aggregate(sourceTable, configJson);
// 或使用手动分组方案：DataTableManualAggregator.Aggregate(sourceTable, configJson);

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

基于JSON配置的DataTable动态聚合查询问题（VB.NET/C#）

问题说明 #

1. 定义JSON配置模型 #

2. 修正后的Dynamic LINQ方案 #

关键修正点 #

3. 非LINQ手动分组方案 #