how to get all columns from a linq result with a join

Question

I've got some different typed lists, which I would like to join and get a dynamic result.

Let's say list1 is the base list. List 2 and 3 are list with extra information. Sometime I want the info and in other runs I don't need (one of) them.

If I need the extra info, I know which columns I want to get.

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 });

assuming both info for list 1 and 2 is needed:

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>();

info from list 3 is sometimes needed (now with true, it will always be added to the result):

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>();
}

This does work, but the result of a and b is combined in one column. Since I will use about 10 list (also with different types) that might or not be joint, it's very hard to know the end result and therefore make something like:

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>();

How can I dynamically get all the results available in different columns, preferably skipping the DBDDate for all the joined lists, so DBDate is just in one column. Regards,

Matthijs

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

Extra info (code) I tried to get the result readable:

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;
    }

And tried this one:

        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

There is no easy way to do this.

Because you are casting it to dynamic anyway you could make use of ExpandoObject together with some helper methods.

You will need the following helpers:

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;
        }
    }
}

And than, simply use .Select(GetFlatExpando) before the call to ToList in all queries:

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>();
}

This code has the nice side-effect that DBDate exists only once.

To make the binding to the data grid work, you need another extension method (put it into the Extensions class from above):

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;
}

Use it like this:

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