C# how to convert IEnumerable anonymous lists into data table

Question

Many solutions are available that convert lists to DataTable that use reflection, and that would work for converting anonymous types. However, it there are lots of lists of anonymous types, then performance can be an issue.

Is this the only way to create a DataTable from a list? Are there faster ways of doing this?

Answer 1

It would absolutely be better to do this using proper named POCO/DTO/etc classes, but it can still be done. The cost of reflection can be removed by using meta-programming, ideally by using a pre-rolled library such as FastMember, as shown below.

Note that the use of anonymous types has forced the use of IList here (rather than IList<T> or List<T> etc). The use of a generic version would be preferable, using named types. This would allow a few changes - in particular, itemType would be typeof(T), and it would be possible to create the correct columns even for an empty table. Perhaps more importantly, it would enforce that the list is homogeneous, rather than having to make an assumption about that.

using FastMember;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
static class Program
{
    static void Main()
    {
        var list = GetList();
        var table = ToTable(list);
    }
    static DataTable ToTable(IList source)
    {
        if (source == null) throw new ArgumentNullException();
        var table = new DataTable();
        if (source.Count == 0) return table;

        // blatently assume the list is homogeneous
        Type itemType = source[0].GetType();
        table.TableName = itemType.Name;
        List<string> names = new List<string>();
        foreach (var prop in itemType.GetProperties())
        {
            if (prop.CanRead && prop.GetIndexParameters().Length == 0)
            {
                names.Add(prop.Name);
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
        }
        names.TrimExcess();

        var accessor = TypeAccessor.Create(itemType);
        object[] values = new object[names.Count];
        foreach (var row in source)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = accessor[row, names[i]];
            }
            table.Rows.Add(values);
        }
        return table;
    }
    static IList GetList()
    {
        return new[] {
            new { foo = "abc", bar = 123},
            new { foo = "def", bar = 456},
            new { foo = "ghi", bar = 789},
        };
    }
}

Answer 2

Improved on @MarcGravell's answer to support:

1. An optional list of fields specifying which columns to keep and their order.

2. Nullable types.

   static public DataTable ToDataTable(this IList anonymousSource, List<string> keepOrderedFieldsOpt = null)
   {
       // https://stackoverflow.com/a/13153479/538763 - @MarcGravell
       // Added keepOrderedFieldsOpt, nullable types - @crokusek
   
       if (anonymousSource == null) throw new ArgumentNullException();
       DataTable table = new DataTable();
       if (anonymousSource.Count == 0) return table;
   
       // blatently assume the list is homogeneous
       Type itemType = anonymousSource[0].GetType();
       table.TableName = itemType.Name;            
   
       // Build up orderedColumns
       //
       List<PropertyInfo> orderedColumns;            
       if (keepOrderedFieldsOpt != null)
       {
           Dictionary<string, PropertyInfo> propertiesByName = itemType.GetProperties()
               .ToDictionary(p => p.Name, p => p);
   
           orderedColumns = new List<PropertyInfo>();
           List<string> missingFields = null;
   
           foreach (string field in keepOrderedFieldsOpt)
           {
               PropertyInfo tempPropertyInfo;
               if (propertiesByName.TryGetValue(field, out tempPropertyInfo))
                   orderedColumns.Add(tempPropertyInfo);
               else
                   (missingFields ?? (missingFields = new List<string>())).Add(field);
           }
   
           if (missingFields != null) 
               throw new ArgumentOutOfRangeException("keepOrderedFieldsOpt", "Argument keepOrderedFieldsOpt contains invalid field name(s): " + String.Join(", ", missingFields));
       }
       else
           orderedColumns = itemType.GetProperties().ToList();
   
       List<string> names = new List<string>();
       foreach (PropertyInfo prop in orderedColumns)
       {
           if (prop.CanRead && prop.GetIndexParameters().Length == 0)
           {
               names.Add(prop.Name);
   
               // Nullable support from stackoverflow.com/a/23233413/538763 - @Damith
               table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
           }
       }
       names.TrimExcess();
   
       TypeAccessor accessor = TypeAccessor.Create(itemType);
       object[] values = new object[names.Count];
       foreach (var row in anonymousSource)
       {
           for (int i = 0; i < values.Length; i++)
               values[i] = accessor[row, names[i]];
   
           table.Rows.Add(values);
       }
       return table;
   }