Best Practice: Convert LINQ Query result to a DataTable without looping
Converting Query result in DataTables Generic Function
DataTable ddt = new DataTable();
ddt = LINQResultToDataTable(query);
public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)
{
DataTable dt = new DataTable();
PropertyInfo[] columns = null;
if (Linqlist == null) return dt;
foreach (T Record in Linqlist)
{
if (columns == null)
{
columns = ((Type)Record.GetType()).GetProperties();
foreach (PropertyInfo GetProperty in columns)
{
Type colType = GetProperty.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dt.Columns.Add(new DataColumn(GetProperty.Name, colType));
}
}
DataRow dr = dt.NewRow();
foreach (PropertyInfo pinfo in columns)
{
dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ? DBNull.Value : pinfo.GetValue
(Record, null);
}
dt.Rows.Add(dr);
}
return dt;
}
Use System.Reflection and iterate for each record in the query object.
Dim dtResult As New DataTable
Dim t As Type = objRow.GetType
Dim pi As PropertyInfo() = t.GetProperties()
For Each p As PropertyInfo In pi
dtResult.Columns.Add(p.Name)
Next
Dim newRow = dtResult.NewRow()
For Each p As PropertyInfo In pi
newRow(p.Name) = p.GetValue(objRow,Nothing)
Next
dtResult.Rows.Add(newRow.ItemArray)
Return dtResult
I am using morelinq.2.2.0 package in asp.net web application, Nuget package manager console
PM> Install-Package morelinq
Namespace
using MoreLinq;
My sample stored procedure sp_Profile() which returns profile details
DataTable dt = context.sp_Profile().ToDataTable();
Use Linq to Dataset. From the MSDN : Creating a DataTable From a Query (LINQ to DataSet)
// Query the SalesOrderHeader table for orders placed
// after August 8, 2001.
IEnumerable<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
select order;
// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();
If you have anonymous types :
From the Coder Blog : Using Linq anonymous types and CopyDataTable
It explains how to use MSDN's How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow