Convert Generic Nested List to Datatable

Question

Continuation from "Convert generic List/Enumerable to DataTable?"

I've been using the following code to covert a generic List<T> into a DataTable:

    public static DataTable ToDataTable<T>(this IList<T> data)
    {
        PropertyDescriptorCollection properties = 
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                 row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }

However, I now have a list List<foo> where foo contains a property List<bar> (where List<bar> can contain zero values).

Question:

I want to convert a generic List<foo> containing another generic nested List<bar> such as :

        List<bar> GenericNestedList = new List<bar>{ new bar("barA"), new bar("barB") };
        List<foo> GenericList = new List<foo> { new foo("fooA", GenericNestedList) };

To result in a DataTable:

| GenericProperty | GenericNestedProperty |
|-----------------|-----------------------|
|      fooA       |         barA          |
|      fooA       |         barB          |

The original code accounts for many properties within foo, bar would also have this requirement.

So far I've been able to retrieve the properties of bar, however I've been unable to figure out how to populate the Datatable when List<bar> is empty. I don't have a lot of experience writing generic methods, so my apologies for not being able to provide a lot of my workings.

Other Examples:

List

List<foo> GenericList = new List<foo> { new foo("fooB", new List<bar>()) };

Datatable

| GenericProperty | GenericNestedProperty |
|-----------------|-----------------------|
|      fooB       |                       |

List

        List<bar> GenericNestedListA = new List<bar>{ new bar("barC"), new bar("barD") };
        List<bar> GenericNestedListB = new List<bar> { new bar("barE"), new bar("barF") };

        List<foo> GenericList = new List<foo> { new foo("fooC", GenericNestedListA),
                                                new foo("fooD", GenericNestedListB) };

Datatable

| GenericProperty | GenericNestedProperty |
|-----------------|-----------------------|
|      fooC       |         barC          |
|      fooC       |         barD          |
|      fooD       |         barE          |
|      fooD       |         barF          |

Classes:

foo

class foo
{
    public string GenericProperty;
    public List<bar> GenericNestedList;

    public foo(string GenericProperty, List<bar> GenericNestedList)
    {
        this.GenericProperty = GenericProperty;
        this.GenericNestedList = GenericNestedList;
    }
}

bar

class bar
{
    public string GenericNestedProperty;

    public bar(string GenericNestedProperty)
    {
        this.GenericNestedProperty = GenericNestedProperty;
    }
}

Answer 1

Just a quick solution:

public DataTable CreateNestedDataTable<TOuter, TInner>(IEnumerable<TOuter> list, string innerListPropertyName)
{
    PropertyInfo[] outerProperties = typeof(TOuter).GetProperties().Where(pi => pi.Name != innerListPropertyName).ToArray();
    PropertyInfo[] innerProperties = typeof(TInner).GetProperties();
    MethodInfo innerListGetter = typeof(TOuter).GetProperty(innerListPropertyName).GetMethod;

    // set up columns
    DataTable table = new DataTable();
    foreach (PropertyInfo pi in outerProperties)
        table.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
    foreach (PropertyInfo pi in innerProperties)
        table.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);

    // iterate through outer items
    foreach (TOuter outerItem in list)
    {
        var innerList = innerListGetter.Invoke(outerItem, null) as IEnumerable<TInner>;
        if (innerList == null || innerList.Count() == 0)
        {
            // outer item has no inner items
            DataRow row = table.NewRow();
            foreach (PropertyInfo pi in outerProperties)
                row[pi.Name] = pi.GetValue(outerItem) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        else
        {
            // iterate through inner items
            foreach (object innerItem in innerList)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo pi in outerProperties)
                    row[pi.Name] = pi.GetValue(outerItem) ?? DBNull.Value;
                foreach (PropertyInfo pi in innerProperties)
                    row[pi.Name] = pi.GetValue(innerItem) ?? DBNull.Value;
                table.Rows.Add(row);
            }
        }
    }

    return table;
}

One could probably expand this even further, so it could work with multiple nested lists, or automatically recognize the properties that are nested lists. But I kept it simple here.

It’s used like this:

var table = CreateNestedDataTable<foo, bar>(GenericList, "GenericNestedList");

Tested with your examples, it produces the desired results:

---

Above code uses PropertyInfo.GetMethod and PropertyInfo.GetValue which were introduced with .NET 4.5. For 4.0, make the following replacements:

// before
typeof(TOuter).GetProperty(innerListPropertyName).GetMethod;

// after
typeof(TOuter).GetProperty(innerListPropertyName).GetGetMethod(true);


// for each row assignment
// before
row[pi.Name] = pi.GetValue(item) ?? DBNull.Value;

// after
row[pi.Name] = pi.GetValue(item, null) ?? DBNull.Value;