LINQ IEnumerable object to a DataTable

Monday, April 11, 2011

LINQ is really a very powerful tool for working with complex entities and datasets. It provides you with an easy way to map data to Entity classes. But, sometimes it is very helpful to have the results returned by a LINQ query map into a DataTable. If your LINQ returns a collection of DataRow objects, you can do this using the inbuilt DataTableExtensions.CopyToDataTable() method. However, if your query returns a collection of a custom entity type, you would need to write a utility to do that.

Below is a method that could be used to convert an IEnumerable entity object to a DataTable:

// C#
/// <summary>
/// Convert a Linq IEnumerable object to a DataTable
/// </summary>
/// <typeparam name="T">Entity Type</typeparam>
/// <param name="enumberable">Linq Resultset</param>
/// <returns>Datatable with results</returns>
public static DataTable LinqToDataTable(this IEnumerable enumberable)
{
    DataTable dt = new DataTable();

    T first = enumberable.FirstOrDefault();
    if (first == null)  // return if the resultset is empty
        return dt;

    Type t = first.GetType();
    PropertyInfo[] properties = t.GetProperties();

    // Adding columns to the datatable from the property info
    foreach (PropertyInfo pi in properties)
    {
        Type pt = pi.PropertyType;

        // Handling nullable properties in the resultset
        if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
            pt = Nullable.GetUnderlyingType(pt);
                
        dt.Columns.Add(pi.Name, pt);
    }

    // Adding rows to the datatable from the enumerable resultset
    foreach (object obj in enumberable)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyInfo pi in properties)
        {
            object value = (pi.GetValue(obj, null) != null) ? pi.GetValue(obj, null) : DBNull.Value;
            dr[pi.Name] = value;
        }
        dt.Rows.Add(dr);
    }

    return dt;
}

' VB.Net
''' <summary>
''' Convert a Linq IEnumerable object to a DataTable
''' </summary>
''' <typeparam name="T">Entity Type</typeparam>
''' <param name="enumberable">Linq Resultset</param>
''' <returns>Datatable with results</returns> _
Public Shared Function LinqToDataTable(Of T)(enumberable As IEnumerable(Of T)) As DataTable
 Dim dt As New DataTable()

 Dim first As T = enumberable.FirstOrDefault()
 If first Is Nothing Then
  ' return if the resultset is empty
  Return dt
 End If

 Dim t As Type = first.[GetType]()
 Dim properties As PropertyInfo() = t.GetProperties()

 ' Adding columns to the datatable from the property info
 For Each pi As PropertyInfo In properties
  Dim pt As Type = pi.PropertyType

  ' Handling nullable properties in the resultset
  If pt.IsGenericType AndAlso pt.GetGenericTypeDefinition() = GetType(Nullable(Of )) Then
   pt = Nullable.GetUnderlyingType(pt)
  End If

  dt.Columns.Add(pi.Name, pt)
 Next

 ' Adding rows to the datatable from the enumerable resultset
 For Each obj As Object In enumberable
  Dim dr As DataRow = dt.NewRow()
  For Each pi As PropertyInfo In properties
   Dim value As Object = If((pi.GetValue(obj, Nothing) IsNot Nothing), pi.GetValue(obj, Nothing), DBNull.Value)
   dr(pi.Name) = value
  Next
  dt.Rows.Add(dr)
 Next

 Return dt
End Function