I have a very strange issue, and no clue which way I should take to fix it.
I have an IEnumerable<Dictionary<string,object>> and it can contain one or more IEnumerable<Dictionary<string,object>>.
Now, this Dictionary needs to be imported into DataTable, and if the IEnumerable<Dictionary<string,object>> inside has 0 children, then the DataTable should contain only one row with the Column names as strings, and the RowData as objects (string in this case). But, if there is a child, then the DataTable should contains the same number of rows as this child, and other information in every row from parent.
For instance, the parent Dictionary has these values:
string, object --------------- Name, Mike LastName, Tyson
IEnumerable dictionary child has:
string, object ---------------- [0] ChildName, John ChildAge, 10 [1] ChildName, Tony ChildAge, 12
Result should be:
Name LastName ChildName ChildAge -------------------------------------------- Mike Tyson John 10 Mike Tyson Tony 12
Also, Parent IEnumerable can have many children IEnumerable, but they will all have the same size.
Does anyone have idea how to solve this?
static void Main(string[] args)
{
var child1 = new List<Dictionary<string, object>>();
var childOneDic = new Dictionary<string, object>
{
{ "ChildName", "John" },
{ "ChildAge", 10 }
};
child1.Add(childOneDic);
var child2 = new List<Dictionary<string, object>>();
var childTwoDic = new Dictionary<string, object>
{
{ "ChildName", "Tony" },
{ "ChildAge", 12 }
};
child2.Add(childTwoDic);
var parent = new List<Dictionary<string, object>>();
var parentDic = new Dictionary<string, object>
{
{ "Name", "Mike" },
{ "LastName", "Tyson" },
{ "child1", child1 },
{ "child2", child2 }
};
parent.Add(parentDic);
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("LastName");
table.Columns.Add("ChildName");
table.Columns.Add("ChildAge");
table = CreateTable(parent, null, table);
}
static DataTable CreateTable(IEnumerable<Dictionary<string, object>> parent,
DataRow row, DataTable table)
{
if (row == null)
{
row = table.NewRow();
}
foreach (var v in parent)
{
foreach (var o in v)
{
if (o.Value.GetType().IsGenericType)
{
var dic = (IEnumerable<Dictionary<string, object>>) o.Value;
CreateTable(dic, row, table);
}
else
{
row[o.Key] = o.Value;
}
}
if (row.RowState == DataRowState.Added)
{
DataRow tempRow = table.NewRow();
tempRow.ItemArray = row.ItemArray;
table.Rows.Add(tempRow);
}
else
{
table.Rows.Add(row);
}
}
return table;
}
Linq is a good candidate for this job. I still think you should rethink about design, this is such a horrible thing to do. This should do (and without any hard coding):
var child1 = new List<IDictionary<string, object>>
{
new Dictionary<string, object> { { "ChildName", "John" }, { "ChildAge", 10 } }
};
var child2 = new List<IDictionary<string, object>>
{
new Dictionary<string, object> { { "ChildName", "Tony" }, { "ChildAge", 12 } }
};
var parent = new List<IDictionary<string, object>>
{
new Dictionary<string, object>
{
{ "Name", "Mike" },
{ "LastName", "Tyson" },
{ "child1", child1 },
{ "child2", child2 }
},
new Dictionary<string, object>
{
{ "Name", "Lykke" },
{ "LastName", "Li" },
{ "child1", child1 },
},
new Dictionary<string, object>
{
{ "Name", "Mike" },
{ "LastName", "Oldfield" }
}
};
CreateTable(parent);
static DataTable CreateTable(IEnumerable<IDictionary<string, object>> parents)
{
var table = new DataTable();
foreach (var parent in parents)
{
var children = parent.Values
.OfType<IEnumerable<IDictionary<string, object>>>()
.ToArray();
var length = children.Any() ? children.Length : 1;
var parentEntries = parent.Where(x => x.Value is string)
.Repeat(length)
.ToLookup(x => x.Key, x => x.Value);
var childEntries = children.SelectMany(x => x.First())
.ToLookup(x => x.Key, x => x.Value);
var allEntries = parentEntries.Concat(childEntries)
.ToDictionary(x => x.Key, x => x.ToArray());
var headers = allEntries.Select(x => x.Key)
.Except(table.Columns
.Cast<DataColumn>()
.Select(x => x.ColumnName))
.Select(x => new DataColumn(x))
.ToArray();
table.Columns.AddRange(headers);
var addedRows = new int[length];
for (int i = 0; i < length; i++)
addedRows[i] = table.Rows.IndexOf(table.Rows.Add());
foreach (DataColumn col in table.Columns)
{
object[] columnRows;
if (!allEntries.TryGetValue(col.ColumnName, out columnRows))
continue;
for (int i = 0; i < addedRows.Length; i++)
table.Rows[addedRows[i]][col] = columnRows[i];
}
}
return table;
}
This is one extension method I've used:
public static IEnumerable<T> Repeat<T>(this IEnumerable<T> source, int times)
{
source = source.ToArray();
return Enumerable.Range(0, times).SelectMany(_ => source);
}
You can create the addedRows variable in a more idiomatic fashion (which I prefer) but may be that's little less readable for others. In a single line, like this:
var addedRows = Enumerable.Range(0, length)
.Select(x => new
{
relativeIndex = x,
actualIndex = table.Rows.IndexOf(table.Rows.Add())
})
.ToArray();
The tricky part here is to get the pivoting right. No big deal in our case since we can utilize indexers. Do test with a set of examples and let me know if this is buggy..
One another way of doing it is to precalculate the headers (data table columns before the loop) as it's not going to change anyway. But that also means one extra round of enumeration. As to which is more efficient, you will have to test it.. I find the first one more elegant though.
static DataTable CreateTable(IEnumerable<IDictionary<string, object>> parents)
{
var table = new DataTable();
//excuse the meaningless variable names
var c = parents.FirstOrDefault(x => x.Values
.OfType<IEnumerable<IDictionary<string, object>>>()
.Any());
var p = c ?? parents.FirstOrDefault();
if (p == null)
return table;
var headers = p.Where(x => x.Value is string)
.Select(x => x.Key)
.Concat(c == null ?
Enumerable.Empty<string>() :
c.Values
.OfType<IEnumerable<IDictionary<string, object>>>()
.First()
.SelectMany(x => x.Keys))
.Select(x => new DataColumn(x))
.ToArray();
table.Columns.AddRange(headers);
foreach (var parent in parents)
{
var children = parent.Values
.OfType<IEnumerable<IDictionary<string, object>>>()
.ToArray();
var length = children.Any() ? children.Length : 1;
var parentEntries = parent.Where(x => x.Value is string)
.Repeat(length)
.ToLookup(x => x.Key, x => x.Value);
var childEntries = children.SelectMany(x => x.First())
.ToLookup(x => x.Key, x => x.Value);
var allEntries = parentEntries.Concat(childEntries)
.ToDictionary(x => x.Key, x => x.ToArray());
var addedRows = Enumerable.Range(0, length)
.Select(x => new
{
relativeIndex = x,
actualIndex = table.Rows.IndexOf(table.Rows.Add())
})
.ToArray();
foreach (DataColumn col in table.Columns)
{
object[] columnRows;
if (!allEntries.TryGetValue(col.ColumnName, out columnRows))
continue;
foreach (var row in addedRows)
table.Rows[row.actualIndex][col] = columnRows[row.relativeIndex];
}
}
return table;
}