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;
}
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With