title: how to get all columns from a linq result with a join
body: <p>I've got some different typed lists, which I would like to join and get a dynamic result.</p>
  
  <p>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.</p>
  
  <p>If I need the extra info, I know which columns I want to get.</p>
  
  <pre><code>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&lt;DateAndValue1&gt; list1,list2;
  List&lt;DateAndValue2&gt; list3;
  
  bool addList2, addList3;
  list1 = new List&lt;DateAndValue1&gt;();
  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&lt;DateAndValue1&gt;();
  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&lt;DateAndValue2&gt;();
  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 });
  </code></pre>
  
  <p>assuming both info for list 1 and 2 is needed:</p>
  
  <pre><code>List&lt;dynamic&gt; 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&lt;dynamic&gt;();
  </code></pre>
  
  <p>info from list 3 is sometimes needed (now with true, it will always be added to the result):</p>
  
  <pre><code>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&lt;dynamic&gt;();
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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&lt;dynamic&gt;();
  </code></pre>
  
  <p>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,</p>
  
  <p>Matthijs</p>
  
  <p>============================================================</p>
  
  <p>Extra info (code) I tried to get the result readable:</p>
  
  <pre><code>public DataTable LINQToDataTable&lt;T&gt;(IEnumerable&lt;T&gt; 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) &amp;&amp; (colType.GetGenericTypeDefinition() == typeof(Nullable&lt;&gt;)))
                              {
                                  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;
      }
  </code></pre>
  
  <p>And tried this one:</p>
  
  <pre><code>        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&lt;T&gt;(IEnumerable&lt;T&gt; 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) &amp;&amp; (colType.GetGenericTypeDefinition() == typeof(Nullable&lt;&gt;)))
                      {
                          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;
      }
  </code></pre>