c# - 如何使用连接从 linq 结果中获取所有列

Question

我有一些不同类型的列表，我想加入这些列表并获得动态结果。

假设 list1 是基本列表。列表 2 和 3 是包含额外信息的列表。有时我需要这些信息，而在其他运行中我不需要它们(其中之一)。

如果我需要额外的信息，我知道我想要获取哪些列。

public struct DateAndValue1
{
   public uint DBDate { get; set; }
   public double Value1 { get; set; }
}

public struct DateAndValue2
{
   public uint DBDate { get; set; }
   public double Value1 { get; set; }
   public bool myBool { get; set; }
   public int someInt { get; set; }
}

List<DateAndValue1> list1,list2;
List<DateAndValue2> list3;

bool addList2, addList3;
list1 = new List<DateAndValue1>();
list1.Add(new DateAndValue1 { DBDate = 1, Value1 = 10 });
list1.Add(new DateAndValue1 { DBDate = 2, Value1 = 20 });
list1.Add(new DateAndValue1 { DBDate = 3, Value1 = 30 });
list1.Add(new DateAndValue1 { DBDate = 4, Value1 = 40 });
list1.Add(new DateAndValue1 { DBDate = 5, Value1 = 50 });
list1.Add(new DateAndValue1 { DBDate = 6, Value1 = 60 });

list2 = new List<DateAndValue1>();
list2.Add(new DateAndValue1 { DBDate = 1, Value1 = 100 });
list2.Add(new DateAndValue1 { DBDate = 1, Value1 = 200 });
list2.Add(new DateAndValue1 { DBDate = 3, Value1 = 300 });
list2.Add(new DateAndValue1 { DBDate = 4, Value1 = 400 });
list2.Add(new DateAndValue1 { DBDate = 5, Value1 = 500 });
list2.Add(new DateAndValue1 { DBDate = 5, Value1 = 600 });

list3 = new List<DateAndValue2>();
list3.Add(new DateAndValue2 { DBDate = 1, Value1 = 1000, myBool = true });
list3.Add(new DateAndValue2 { DBDate = 2, Value1 = 2000, myBool = true });
list3.Add(new DateAndValue2 { DBDate = 2, Value1 = 3000, myBool = true });
list3.Add(new DateAndValue2 { DBDate = 4, Value1 = 4000, myBool = true });
list3.Add(new DateAndValue2 { DBDate = 6, Value1 = 5000, myBool = true });
list3.Add(new DateAndValue2 { DBDate = 6, Value1 = 6000, myBool = true });

假设需要列表 1 和 2 的信息:

List<dynamic> result = (from a in list1
                        join b in list2
                        on a.DBDate equals b.DBDate
                        select new { DBDate = a.DBDate, Result_A1 = a.Value1, Result_B1 = b.Value1 }).ToList<dynamic>();

有时需要列表 3 中的信息(现在为 true，它将始终添加到结果中):

if(true)
{
    result = (from so_far in result
              join c in list3
              on so_far.a.DBDate equals c.DBDate
              select new {so_far, Result_C1 = c.Value1,Result_C2=c.myBool }).ToList<dynamic>();
}

这确实有效，但 a 和 b 的结果合并在一列中。因为我将使用大约 10 个可能联合或不联合的列表(也有不同的类型)，所以很难知道最终结果，因此做出如下内容:

result = (from so_far in result
        join c in list3
        on so_far.a.DBDate equals c.DBDate
        select new {DBDate= so_far.DBDate, Result_A1=so_far.Result_A1,Result_B1=so_far.Result_B1 , Result_C1 = c.Value1,Result_C2=c.myBool }).ToList<dynamic>();

如何动态获取不同列中的所有可用结果，最好跳过所有连接列表的 DBDDate，因此 DBDate 仅在一列中。问候，

马蒂斯

============================================= =============

额外信息(代码)我试图让结果可读:

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
    {
            DataTable dtReturn = new DataTable();

            PropertyInfo[] columnNames = null;

            if(varlist == null)
                return dtReturn;

            try
            {
                foreach(T rec in varlist)
                {
                    if(columnNames == null)
                    {
                        columnNames = ((Type)rec.GetType()).GetProperties();
                        foreach(PropertyInfo pi in columnNames)
                        {
                            Type colType = pi.PropertyType;

                            if((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                            {
                                colType = colType.GetGenericArguments()[0];
                            }

                            dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                        }
                    }

                    DataRow dr = dtReturn.NewRow();

                    foreach(PropertyInfo pi in columnNames)
                    {
                        dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                        (rec, null);
                    }

                    dtReturn.Rows.Add(dr);
                }
            }
            catch
            {
                return dtReturn;
            }
            return dtReturn;
    }

并尝试了这个:

        private class NestedPropertyInfo
    {
        public PropertyInfo Parent { get; set; }
        public PropertyInfo Child { get; set; }
        public string Name { get { return Parent.Name + "_" + Child.Name; } }
    }

    public DataTable LINQMultipleSelectToDataTable<T>(IEnumerable<T> varlist)
    {
        DataTable dtReturn = new DataTable();
        NestedPropertyInfo[] columns = null;

        if(varlist == null)
            return dtReturn;

        foreach(T rec in varlist)
        {
            if(columns == null)
            {
                columns = (
                    from p1 in rec.GetType().GetProperties()
                    from p2 in p1.PropertyType.GetProperties()
                    select new NestedPropertyInfo { Parent = p1, Child = p2 }
                    ).ToArray();

                foreach(var column in columns)
                {
                    var colType = column.Child.PropertyType;

                    if((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }

                    dtReturn.Columns.Add(new DataColumn(column.Name, colType));
                }
            }

            DataRow dr = dtReturn.NewRow();

            foreach(var column in columns)
            {
                var parentValue = column.Parent.GetValue(rec, null);
                var childValue = parentValue == null ? null : column.Child.GetValue(parentValue, null);
                dr[column.Name] = childValue ?? DBNull.Value;
            }

            dtReturn.Rows.Add(dr);
        }

        return dtReturn;
    }

Answer 1

没有简单的方法可以做到这一点。

因为无论如何您都将其转换为 dynamic，所以您可以将 ExpandoObject 与一些辅助方法一起使用。

您将需要以下助手:

public dynamic GetFlatExpando(object o)
{
    IDictionary<string, object> result = new ExpandoObject();

    foreach(var property in o.GetType().GetProperties())
    {
        var value = property.GetValue(o, null);
        var expando = value as ExpandoObject;
        if(expando == null)
            result[property.Name] = value;
        else
            expando.CopyInto(result);
    }

    return result;
}

public static class Extensions
{
    public static void CopyInto(this IDictionary<string, object> source,
                                IDictionary<string, object> target)
    {
        foreach(var member in source)
        {
            target[member.Key] = member.Value;
        }
    }
}

然后，只需在所有查询中调用 ToList 之前使用 .Select(GetFlatExpando):

List<dynamic> result = (from a in list1
                        join b in list2
                        on a.DBDate equals b.DBDate
                        select new { DBDate = a.DBDate, Result_A1 = a.Value1,
                                     Result_B1 = b.Value1 })
                        .Select(GetFlatExpando)
                        .ToList<dynamic>();

if(true)
{
    result = (from so_far in result
              join c in list3
              on so_far.DBDate equals c.DBDate
              select new {so_far, Result_C1 = c.Value1,Result_C2=c.myBool })
              .Select(GetFlatExpando)
              .ToList<dynamic>();
}

这段代码有一个很好的副作用，即 DBDate 只存在一次。

要绑定(bind)到数据网格，您需要另一种扩展方法(将其放入上面的 Extensions 类中):

public static DataTable ToDataTable(this IEnumerable<IDictionary<string, object>> source)
{
    var result = new DataTable();

    foreach(var rowData in source)
    {
        var row = result.NewRow();

        if(result.Columns.Count == 0)
        {
            foreach(var columnData in rowData)
            {
                var column = new DataColumn(columnData.Key,
                                            columnData.Value.GetType())
                result.Columns.Add(column);
            }
        }

        foreach(var columnData in rowData)
            row[columnData.Key] = columnData.Value;
        result.Rows.Add(row);
    }

    return result;
}

像这样使用它:

var dataTable = result.Cast<IDictionary<string, object>>()
                      .ToDataTable();