How to get all columns from linq result with join

Question

I have several different typed lists that I would like to combine and get a dynamic result.

Let's say list1 is a base list. List 2 and 3 are a list with additional information. Sometimes I need information, but in other runs I do not need (one of them).

If I need more information, 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 that information 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>(); 

the information from list 3 is sometimes necessary (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 works, but the result of a and b is combined into one column. Since I will use about 10 lists (also with different types), which may or may not be articular, it is very difficult to find out the final result and, therefore, do 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>(); 

As I can dynamically get all the results available in different columns, it is preferable to skip DBDDate for all merged lists, so DBDate is only in one column. Respectfully,

Matthijs

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

Additional information (code) I tried to get the result for reading:

 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:

  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.

Since you are producing it before dynamic , you can use ExpandoObject along 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 just use .Select(GetFlatExpando) before calling 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 a good side effect that DBDate only exists once.

To bind to the data grid, you need another extension method (put it in the Extensions class at the top):

 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 as follows:

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