Fastest way to fill DataTable from LINQ query using DataContext

Question

I am trying to run a linq query but I need the result as a datatable as I am using that to store records from different queries in the same viewstate object.

The 2 versions below compile, but return an empty set. The exact error is "Value cannot be null. Parameter name: source". (and yes I have 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 don't seem to work in this situation either.

I guess I could just run a sql query command the old-fashioned way, but isn't linq supposed to be quicker?

Solution

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;
}

Comments

[JumpingJezza]

I see - so it is just hiding the invalidcastexception? How do I get it into the datatable then?

[Adam Robinson]

@JumpingJezza: Yes; as is to be used in cases where you know that the object might not actually be the type you're expecting and isn't an "exceptional" case. The as keyword evaluates to null if the cast is invalid, and (as a result) can only be used with reference types and Nullable<T>. See the edit I just posted for a sample extension method that can turn any IEnumerable<T> to a DataTable.

[JumpingJezza]

Perfect! Plus I found out something new about as :)

[PBMe_HikeIt]

I had to built in a check for Nullable Types since the output.Columns.Add call will fail if type is nullable