c# - 如何将两个数据表加入另一个数据表

Question

我有两个数据表table1和table2。我想加入它们，从而在C#. 这两个表的所有列都是未知的。

这是我尝试过的：

var joinTables = from t1 in table1.AsEnumerable()
                 join t2 in table2.AsEnumerable() on t1["S_InstanceID"] equals t2["P_ID"]
                 select new { T1 = t1, T2 = t2 };

注意：两个表中没有同名的列

table1将始终只有一行，但table2可以有任意数量的行。

不知道所有列，只知道必须用于连接关系的S_InstanceIDfromtable1和P_IDfromtable2

目前在joinTablesvar 中，我只得到anonymous DataRow- 我怎样才能得到它的DataTable格式？

标签： c#asp.net.netdatatable

Answer 1

我使用此扩展方法将 LINQ 结果转换回DataTable专门用于join：

// Create new DataTable from LINQ results on DataTable
// Expect T to be anonymous object of form new { DataRow d1, DataRow d2, ... }
public static DataTable FlattenToDataTable<T>(this IEnumerable<T> src) {
    var res = new DataTable();
    if (src.Any()) {
        var firstRow = src.First();
        var rowType = typeof(T);
        var memberInfos = rowType.GetPropertiesOrFields();
        var allDC = memberInfos.SelectMany(mi => mi.GetValue<DataRow>(firstRow).Table.DataColumns());

        foreach (var dc in allDC) {
            var newColumnName = dc.ColumnName;
            if (res.ColumnNames().Contains(newColumnName)) {
                var suffixNumber = 1;
                while (res.ColumnNames().Contains($"{newColumnName}.{suffixNumber}"))
                    ++suffixNumber;
                newColumnName = $"{newColumnName}.{suffixNumber}";
            }
            res.Columns.Add(new DataColumn(newColumnName, dc.DataType));
        }

        foreach (var objRows in src)
            res.Rows.Add(memberInfos.SelectMany(mi => mi.GetValue<DataRow>(objRows).ItemArray).ToArray());
    }
    return res;
}

// ***
// *** Type Extensions
// ***
public static List<MemberInfo> GetPropertiesOrFields(this Type t, BindingFlags bf = BindingFlags.Public | BindingFlags.Instance) =>
    t.GetMembers(bf).Where(mi => mi.MemberType == MemberTypes.Field | mi.MemberType == MemberTypes.Property).ToList();

// ***
// *** MemberInfo Extensions
// ***
public static object GetValue(this MemberInfo member, object srcObject) {
    switch (member) {
        case FieldInfo mfi:
            return mfi.GetValue(srcObject);
        case PropertyInfo mpi:
            return mpi.GetValue(srcObject);
        default:
            throw new ArgumentException("MemberInfo must be of type FieldInfo or PropertyInfo", nameof(member));
    }
}
public static T GetValue<T>(this MemberInfo member, object srcObject) => (T)member.GetValue(srcObject);