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