从两个数据表中查找公共列并将其用于 LINQ 中的联接条件

Question

我有两个数据表，它们是完全动态的。这些将在运行时生成。现在我想通过查找公共列来连接这些表。

请检查下面的代码以获取更多信息

public DataTable DataTableJoiner(DataTable dt1, DataTable dt2)
{
    using (DataTable targetTable = dt1.Clone())
    {
        var dt2Query = dt2.Columns.OfType<DataColumn>().Select(dc =>
            new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
        var dt2FilterQuery = from dc in dt2Query.AsEnumerable()
                             where targetTable.Columns.Contains(dc.ColumnName) == false
                             select dc;
        targetTable.Columns.AddRange(dt2FilterQuery.ToArray());
        var rowData=from row1 in dt1.AsEnumerable()
                    join row2 in dt2.AsEnumerable()
                    on row1.Field<int>("ID") equals row2.Field<int>("ID")
                    select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();
        foreach (object[] values in rowData) targetTable.Rows.Add(values); 
        return targetTable;
    }
}

在上面我已将“ID”硬编码为公共列。我需要动态生成/识别公共列。请帮我。

Answer 1

这对我有用：

private DataTable DataTableJoiner(DataTable dt1, DataTable dt2)
    {
        var commonColumns = dt1.Columns.OfType<DataColumn>().Intersect(dt2.Columns.OfType<DataColumn>(), new DataColumnComparer());

        var result = new DataTable();
        result.Columns.AddRange(
            dt1.Columns.OfType<DataColumn>()
            .Union(dt2.Columns.OfType<DataColumn>(), new DataColumnComparer())
            .Select(c => new DataColumn(c.Caption, c.DataType, c.Expression, c.ColumnMapping))
            .ToArray());

        var rowData = dt1.AsEnumerable().Join(
            dt2.AsEnumerable(),
            row => commonColumns.Select(col => row[col.Caption]).ToArray(),
            row => commonColumns.Select(col => row[col.Caption]).ToArray(),
            (row1, row2) => 
            {
                var row = result.NewRow();
                row.ItemArray = result.Columns.OfType<DataColumn>().Select(col => row1.Table.Columns.Contains(col.Caption) ? row1[col.Caption] : row2[col.Caption]).ToArray();
                return row;
            },
            new ObjectArrayComparer());

        foreach (var row in rowData)
            result.Rows.Add(row);

        return result;
    }

为了让它工作，你需要另外声明这两个类：

private class DataColumnComparer : IEqualityComparer<DataColumn>
    {

        #region IEqualityComparer<DataColumn> Members

        public bool Equals(DataColumn x, DataColumn y)
        {
            return x.Caption == y.Caption;
        }

        public int GetHashCode(DataColumn obj)
        {
            return obj.Caption.GetHashCode();
        }

        #endregion
    }

    private class ObjectArrayComparer : IEqualityComparer<object[]>
    {
        #region IEqualityComparer<object[]> Members

        public bool Equals(object[] x, object[] y)
        {
            for (var i = 0; i < x.Length; i++)
            {
                if (!object.Equals(x[i], y[i]))
                    return false;
            }

            return true;
        }

        public int GetHashCode(object[] obj)
        {
            return obj.Sum(item => item.GetHashCode());
        }

        #endregion
    }

我希望这有帮助！