c# - 将 DataTable 中的重复行合并到一条记录中并更新某些列

Question

我的 C# 代码中有一个 Datatable

DataTable dtDetails;

我的数据表如何记录；

id | tid | code | pNameLocal        | qty | price
-------------------------------------------------
1  |101  | 101  | some_local_name   | 2   |20.36
2  |102  | 202  | some_local_name   | 1   |15.30 // exactly same entry 
3  |102  | 202  | some_local_name   | 1   |15.30 // exactly same entry
4  |102  | 202  | some_local_name   | 1   |10.00 //same entry as same tid but price is different
5  |102  | 202  | some_local_name   | 2   |15.30 //same entry as same tid but different qty
6  |102  | 202  | some_local_name2  | 1   |15.30 //same entry as same tid but pNameLocal different
7  |103  | 202  | some_local_name   | 1   |15.30 // different entry of same product see different tid
8  |104  | 65   | some_local_name   | 5   |05.00
9  |105  | 700  | some_local_name   | 2   |07.01 // does not exist in "dtProduct"

要做的是将完全相同但多次输入的记录合并为一个，但保持它们的 qty 列和 price 列更新，例如在上面DaraRows id 2 和 3 与 id 1 有完全相同的记录，这应该是一条记录并更新 qty 和 price 通过添加完全相同的重复记录。这应该在相同的 DataTable 或新的中更新。

Answer 1

您可以将 DataTable 视为可枚举的，并使用 Linq 来创建新的 DataTable。代码看起来像这样:

    DataTable newDt = dt.AsEnumerable()
        .GroupBy(r => r.Field<int>("tid"))
        .Select(g => {
            var row = dt.NewRow();

            row["tid"] = g.Key;
            row["code"] = g.First(r => r["code"] != null).Field<int>("code");
            row["pNameLocal"] = g.First(r => r["pNameLocal"] != null).Field<string>("pNameLocal");
            row["qty"] = g.Sum(r => r.Field<int>("qty"));
            row["price"] = g.Sum(r => r.Field<double>("price"));

            return row;
        }).CopyToDataTable();

请参阅下面的示例控制台应用程序，设置示例数据、执行分组并显示原始数据表和结果数据表。

using System;
using System.Data;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
    static void Main(string[] args)
    {
        var dt = FillUpTestTable();
        DumpDataTable(dt);
        DataTable newDt = dt.AsEnumerable()
            .GroupBy(r => r.Field<int>("tid"))
            .Select(g => {
                var row = dt.NewRow();

                row["tid"] = g.Key;
                row["code"] = g.First(r => r["code"] != null).Field<int>("code");
                row["pNameLocal"] = g.First(r => r["pNameLocal"] != null).Field<string>("pNameLocal");
                row["qty"] = g.Sum(r => r.Field<int>("qty"));
                row["price"] = g.Sum(r => r.Field<double>("price"));

                return row;
            }).CopyToDataTable();

        Console.WriteLine();
        Console.WriteLine("Result: ");
        Console.WriteLine();

        DumpDataTable(newDt);
        Console.ReadLine();
    }

    private static DataTable FillUpTestTable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("id", typeof(int));
        dt.Columns.Add("tid", typeof(int));
        dt.Columns.Add("code", typeof(int));
        dt.Columns.Add("pNameLocal", typeof(string));
        dt.Columns.Add("qty", typeof(int));
        dt.Columns.Add("price", typeof(double));

        dt.Rows.Add(1, 101, 101, "some_local_name", 2, 20.36);
        dt.Rows.Add(2, 102, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(3, 102, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(4, 102, 202, "some_local_name", 2, 10.00);
        dt.Rows.Add(5, 102, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(6, 102, 202, "some_local_name2", 1, 15.30);
        dt.Rows.Add(7, 103, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(8, 104, 202, "some_local_name", 2, 05.00);
        dt.Rows.Add(9, 105, 202, "some_local_name", 2, 07.01);

        return dt;
    }
    private static void DumpDataTable(DataTable newDt)
    {
        foreach (DataRow dataRow in newDt.Rows)
        {
            foreach (var item in dataRow.ItemArray)
            {
                Console.Write(item + "   | ");
            }
            Console.WriteLine();
        }
    }
}