We have been adopting Linq slowly but surely and have run into some issues we never had to deal with in the past. Currently I use DataSets in VS2005 for everything. These are nice since they return DataTables easily and allow updates / inserts / etc without issue.
When we dove into Linq and had to return a DataTable for the first time we were stuck. Looking through examples & searching online did not return much. I did find an example by Kevin Goff in CoDe Magazine which outlined 13 tips for Linq. This worked great except I had some issues with null datatypes. Those issues should be fixed in the sample below.
The following code outlines a quick way using Linq to query a database and return the results to a DataTable.
Here is my newly created class:
static
public class ConvertDataTable
{
public static DataTable ToADOTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
{
DataTable dtReturn = new DataTable();
// Could add a check to verify that there is an element 0
T TopRec = varlist.ElementAt(0);
// Use reflection to get property names, to create table
// column namesPropertyInfo[] oProps =
((
Type)TopRec.GetType()).GetProperties();foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}foreach (T rec in varlist)
{
DataRow dr = dtReturn.NewRow();foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
}
dtReturn.Rows.Add(dr);
}
return (dtReturn);
}
public delegate object[] CreateRowDelegate<T>(T t);
}
Here is the usage in code:
LData.MyDataContext db = new LData.MyDataContext(); var query = from p in db.Extensions
where p.DepartmentNumber == "412"select p;
DataTable dt = query.ToADOTable(rec => new object[] { query });
Hope this helps someone else out.
~ Steve