c# - 将每个 id 的数据合并到一行并删除重复项

Question

我需要 LINQ 查询方面的帮助。我有一个表，其中有这样的数据结构：

Employee ID|Action|Manager     |HR BP  |Location     |EffectiveDate|
000001     |UPDATE|John        |Martha |Berlin       |2020-04-10   |
000003     |UPDATE|Fillip      |""     |London       |2020-04-10   |
000002     |UPDATE|Thomas      |Julia  |Madrit       |2020-04-10   |
000001     |UPDATE|John        |""     |""           |2020-04-10   |
000003     |UPDATE|""          |""     |London       |2020-04-10   |
000002     |UPDATE|""          |""     |Madrit       |2020-04-10   |

我需要将每个员工的数据合并到一行并删除重复项：

Employee ID|Action|Manager     |HR BP  |Location     |EffectiveDate|
000001     |UPDATE|John        |Martha |Berlin       |2020-04-10   |
000003     |UPDATE|Fillip      |""     |London       |2020-04-10   |
000002     |UPDATE|Thomas      |Julia  |Madrit       |2020-04-10   |

在互联网上，我发现了这样的 linq 查询，但它没有给我答案：

string ErrorMessage = "";
DataTable CollectionOut = new DataTable();
DataTable dt1 = new DataTable();
try
{
    dt1 = CollectionIn;

    DataTable dtMerged = dt1.AsEnumerable()
                        .GroupBy(r => new {d= r.Field<string>("Employee ID"), f=r.Field<string>("EffectiveDate") })
                        .Select(g => g.First()).CopyToDataTable();
    CollectionOut = dtMerged;
}
catch (Exception ex)
{
    ErrorMessage = ex.Message.ToString();

}

谢谢您的支持！

标签： c#linq

Answer 1

这里的概念是我们将按他们分组，期望group.count总是为 2。然后我们使用第一行合并并循环所有列。如果该列为空，则使用第二行中的相应值。

假设我有像这样的数据行

        mTest.Columns.Add("Employee ID");
        mTest.Columns.Add("Action");
        mTest.Columns.Add("Manager");
        mTest.Columns.Add("HR BP");
        mTest.Columns.Add("Location");
        mTest.Columns.Add("EffectiveDate");

        mTest.Rows.Add("000001", "UPDATE", "", "Martha", "", "2020-04-10");
        mTest.Rows.Add("000003", "UPDATE", "Fillip", "", "London", "2020-04-10");
        mTest.Rows.Add("000002", "UPDATE", "Thomas", "", "Madrit", "2020-04-10");
        mTest.Rows.Add("000002", "UPDATE", "Thomas", "Julia", "Madrit", "2020-04-10");
        mTest.Rows.Add("000001", "UPDATE", "John", "", "Berlin", "2020-04-10");
        mTest.Rows.Add("000003", "UPDATE", "Fillip", "", "London", "2020-04-10");
        mTest.Rows.Add("000003", "UPDATE", "", "", "London", "2020-04-10");
        mTest.Rows.Add("000002", "UPDATE", "", "Julia", "", "2020-04-10");

        dataGridView.DataSource = mTest;

然后我实现组合功能

    private DataTable Combination(DataTable dt)
    {
        List<DataRow> rows = new List<DataRow>();

        foreach (var group in dt.AsEnumerable().GroupBy(x => x.Field<string>("Employee ID")).Select(x => x.ToList()))
        {
            if (group.Count() == 1)
            {
                rows.Add(group[0]);
                continue;
            }

            for (int i = 0; i < group[0].ItemArray.Length; i++)
            {
                if (string.IsNullOrEmpty(group[0][i].ToString()))
                    group[0][i] = group[1][i];
            }

            rows.Add(group[0]);
        }

        // Using DataTableExtensions
        return rows.CopyToDataTable();
    }

结果

dataGridView.DataSource = Combination(mTest);