使用Linq对DataTable多列分组求和返回DataTable代码报错如何解决

错误截图 #

问题排查与修复方案 #

核心错误点 #

• 扩展方法调用错误：你代码中写的.PropertiesToDataTable(groupdatatable)不符合方法定义，该泛型扩展方法无需手动传入类型参数，泛型会自动根据Select返回的groupdatatable类型推断，直接调用无参的.PropertiesToDataTable()即可。
• 潜在空值异常：如果原DataTable中grossProfit、traffic、boxes列存在DBNull空值，直接调用Field<T>会抛出异常，需要增加空值兼容处理。
• 扩展方法兼容问题：原扩展方法未处理可空类型属性、空数据源的场景，容易触发异常。

修改后完整代码 #

分组查询逻辑

DataTable groupFile = dataTable.AsEnumerable()
    .GroupBy(x => new
    {
        CompanyName = x.Field<string>("CompanyName"),
        POS = x.Field<Int32>("POS#"),
        storeName = x.Field<string>("storeName"),
        SpecialIdentifier = x.Field<Int32>("SpecialIdentifier"),
        EmployeeName = x.Field<string>("EmployeeName"),
        processDate = x.Field<DateTime>("processDate"),
        dow = x.Field<string>("dow"),
        monthsName = x.Field<string>("monthsName"),
        years = x.Field<Int32>("years"),
        MonthDay = x.Field<Int32>("MonthDay"),
        processHour = x.Field<Int32>("processHour"),
        monitored = x.Field<string>("monitored")
    })
    .Select(x => new groupdatatable
    {
        CompanyName = x.Key.CompanyName,
        POS = x.Key.POS,
        storeName = x.Key.storeName,
        SpecialIdentifier = x.Key.SpecialIdentifier,
        EmployeeName = x.Key.EmployeeName,
        processDate = x.Key.processDate,
        dow = x.Key.dow,
        monthsName = x.Key.monthsName,
        years = x.Key.years,
        MonthDay = x.Key.MonthDay,
        processHour = x.Key.processHour,
        // 兼容空值，空值默认按0求和
        grossProfit = x.Sum(z => z.Field<Decimal?>("grossProfit") ?? 0),
        traffic = x.Sum(z => z.Field<int?>("traffic") ?? 0),
        boxes = x.Sum(z => z.Field<int?>("boxes") ?? 0),
        monitored = x.Key.monitored
    })
    // 直接调用无参扩展方法即可
    .PropertiesToDataTable();

优化后的扩展方法

public static DataTable PropertiesToDataTable<T>(this IEnumerable<T> source)
{
    // 空数据源直接返回空表
    if (source == null || !source.Any())
        return new DataTable();

    DataTable dt = new DataTable();
    var props = TypeDescriptor.GetProperties(typeof(T));
    foreach (PropertyDescriptor prop in props)
    {
        // 兼容可空类型的列定义
        Type columnType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
        DataColumn dc = dt.Columns.Add(prop.Name, columnType);
        dc.Caption = prop.DisplayName;
        dc.ReadOnly = prop.IsReadOnly;
    }
    foreach (T item in source)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyDescriptor prop in props)
        {
            // 空值转DBNull避免赋值报错
            dr[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
        }
        dt.Rows.Add(dr);
    }
    return dt;
}

实体类无需修改，保持原有定义即可

public class groupdatatable
{
    public string CompanyName { get; set; }
    public Int32 POS { get; set; }
    public string storeName { get; set; }
    public Int32 SpecialIdentifier { get; set; }
    public string EmployeeName { get; set; }
    public DateTime processDate { get; set; }
    public string dow { get; set; }
    public string monthsName { get; set; }
    public Int32 years { get; set; }
    public Int32 MonthDay { get; set; }
    public Int32 processHour { get; set; }
    public Decimal grossProfit { get; set; }
    public Int32 traffic { get; set; }
    public Int32 boxes { get; set; }
    public string monitored { get; set; }
}

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