LINQ: IEnumerable to DataTable

Over the past several months I’ve been promoting LINQ pretty heavily at work.  Several of my coworkers have jumped on the bandwagon and are realizing how much power is available to them.

This week two of my coworkers were working on unrelated projects but both needed to convert a list of simple objects to a DataTable and asked me for an easy way to do it.  LINQ to DataSet provides wonderful functionality for exposing DataTables to LINQ expressions and converting the data into another structure but it doesn’t have anything for turning a collection of objects into a DataTable.  Lucky for us LINQ makes this task really easy.

First we need to use reflection to get the properties for the type we’re converting to a DataTable.

var props = typeof(MyClass).GetProperties();

Once we have our property list we build the structure of the DataTable by converting the PropertyInfo[] into DataColumn[].  We can add each DataColumn to the DataTable at one time with the AddRange method.

var dt = new DataTable();
dt.Columns.AddRange(
  props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray()
);

Now that the structure is defined all that’s left is to populate the DataTable.  This is also trivial since the Add method on the Rows collection has an overload that accepts params object[] as an argument.  With LINQ we can easily build a list of property values for each object, convert that list to an array, and pass it to the Add method.

source.ToList().ForEach(
  i => dt.Rows.Add(props.Select(p =>; p.GetValue(i, null)).ToArray())
);

That’s all there is to it for collections of simple objects.  Those familiar with LINQ to DataSet might note that the example doesn’t use the CopyToDataTable extension method.  The main reason for adding the rows directly to the DataTable instead of using CopyToDataTable is that we’d be doing extra work.  CopyToDataTable accepts IEnumerable but constrains T to DataRow.  In order to make use of the extension method (or its overloads) we still have to iterate over the source collection to convert each item into a DataRow, add each row into a collection, then call CopyToDataTable with that collection.  By adding the rows directly to the DataTable we avoid the extra step altogether.

We can now bring the above code together into a functional example. To run this example open LINQPad, change the language selection to C# Program, and paste the code into the snippet editor.

class MyClass
{
  public Guid ID { get; set; }
  public int ItemNumber { get; set; }
  public string Name { get; set; }
  public bool Active { get; set; }
}

IEnumerable<MyClass> BuildList(int count)
{
  return Enumerable
    .Range(1, count)
    .Select(
      i =>
      new MyClass()
      {
        ID = Guid.NewGuid(),
        ItemNumber = i,
        Name = String.Format("Item {0}", i),
        Active = (i % 2 == 0)
      }
    );
}

DataTable ConvertToDataTable<TSource>(IEnumerable<TSource> source)
{
  var props = typeof(TSource).GetProperties();

  var dt = new DataTable();
  dt.Columns.AddRange(
    props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray()
  );

  source.ToList().ForEach(
    i => dt.Rows.Add(props.Select(p => p.GetValue(i, null)).ToArray())
  );

  return dt;
}

void Main()
{
  var dt = ConvertToDataTable(
    BuildList(100)
  );

  // NOTE: The Dump() method below is a LINQPad extension method.
  //       To run this example outside of LINQPad this method
  //       will need to be revised.

  Console.WriteLine(dt.GetType().FullName);
  dt.Dump();
}

Of course there are other ways to accomplish this and the full example has some holes but it’s pretty easy to expand. An obvious enhancement would be to rename the ConvertToDataTable method and change it to handle child collections and return a full DataSet.

Advertisements