Fastest way to fill DataTable from LINQ query using DataContext
Your problem is this:
as IEnumerable<DataRow>
The as
keyword performs a safe cast, not a conversion, which it seems like you might think that it's doing. The as
keyword is semantically the same as doing this:
IEnumerable<DataRow> queryProjects = (IEnumerable<DataRow>)(from DataRow p in db.STREAM_PROJECTs.AsEnumerable() where p.Field<int>("STREAM_ID") == StreamID select new { PROJECT_ID = p.Field<int>("PROJECT_ID"), PROJECT_NAME = p.Field<int>("PROJECT_NAME") });
Except the version with as
won't throw an exception when it fails to cast your query object (which is an IQueryable<T>
, where T
is an anonymous type) to an IEnumerable<DataRow>
(which it isn't).
Unfortunately, there is no built-in method that I'm aware of that will take an enumerable of a concrete type (like your anonymous type in this example) and turn it into a DataTable
. Writing one wouldn't be too complicated, as you'd essentially need to get the properties reflectively then iterate over the collection and use those properties to create columns in a DataTable
. I'll post an example in a few.
Something like this, placed in a static class within a namespace that you're using
, should provide an extension method that will do what you want:
public static DataTable ToDataTable<T>(this IEnumerable<T> source){ PropertyInfo[] properties = typeof(T).GetProperties(); DataTable output = new DataTable(); foreach(var prop in properties) { output.Columns.Add(prop.Name, prop.PropertyType); } foreach(var item in source) { DataRow row = output.NewRow(); foreach(var prop in properties) { row[prop.Name] = prop.GetValue(item, null); } output.Rows.Add(row); } return output;}