The fastest way to populate a DataTable from a LINQ query using a DataContext

Question

I am trying to run a linq query, but I need the result as datatable, since I use this to store records from different queries in one viewstate.

The 2 versions below compile, but return an empty set. The exact error is: "The value cannot be zero. Parameter name: source." (And yes, I checked there is data):

MyDatabaseDataContext db = new MyDatabaseDataContext(conn); IEnumerable<DataRow> queryProjects = (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<string>("PROJECT_NAME") }) as IEnumerable<DataRow>; DataTable results = queryProjects.CopyToDataTable<DataRow>(); 

...

 //(from p in db.STREAM_PROJECTs.AsEnumerable() //where p.STREAM_ID == StreamID //select new //{ // p.PROJECT_NAME, // p.PROJECT_ID //}) as IEnumerable<DataRow>; 

The examples in this thread do not seem to work in this situation either.

I assume that I could just run the sql query command in the old fashioned way, but shouldn't linq be faster?

Answer 1

Your problem is this:

 as IEnumerable<DataRow> 

The as keyword performs safe selection, not a conversion, which seems like you might think that it does. The as keyword is semantically the same:

 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 that the version with as will not throw an exception when it cannot pass your query object (which is IQueryable<T> , where T is an anonymous type) to IEnumerable<DataRow> (which it is "t).

Unfortunately, there is no built-in method that I know of that will enumerate a specific type (for example, your anonymous type in this example) and turn it into a DataTable . Writing one would not be too complicated, since you would essentially need to reflect the properties, then iterate over the collection and use these properties to create the columns in the DataTable . I will give some examples.

Something like this, placed in a static class in the namespace that you 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; }