convert query result to datatable

I don't use the CopyToDataTable at all because it doesn't seem to be able to handle anonymous types. Instead there is a great solution in "Linq Extensions". There are several sources and they can differ greatly.

The extension I use more often than any other is as follows:

using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Reflection;
    public static class Extensions
    {

        public static DataTable AsDataTable<t>(this IEnumerable<t> enumberable)
        {
            DataTable table = new DataTable("Generated");

            T first = enumberable.FirstOrDefault();
            if (first == null)
                return table;

            PropertyInfo[] properties = first.GetType().GetProperties();
            foreach (PropertyInfo pi in properties)
                table.Columns.Add(pi.Name, pi.PropertyType);
            try
            {
                foreach (T t in enumberable)
                {
                    DataRow row = table.NewRow();
                    foreach (PropertyInfo pi in properties)
                        row[pi.Name] = t.GetType().InvokeMember(pi.Name, BindingFlags.GetProperty, null, t, null);
                    table.Rows.Add(row);
                }
            }
            catch (Exception ex)
            {
                return new DataTable();
            }
            return table;
        }
}
</t></t>

With this extention you can set the names of the columns you want to include such as :

//I prefer this format of extensable queries, but it doesn't matter
DataTable table = dbContext.Personaldetails
                    .Where(i=>i.ID==1)
                    .Select(i=>
                      Forename = i.forename,
                      Surname = i.surname,
                      Sex = i.is_male?"Male":"Female"
                      dob)
                    .AsDataTable();

//or in your preferred format:
IQueriable<personaldetails> query = from i in dbContext.Personaldetails
                                    where i.ID == 1
                                    select new(){
                                      Forename = i.forename,
                                      Surname = i.surname,
                                      Sex = i.is_male?"Male":"Female"
                                      dob)
                                    });
DataTable table = query.AsDataTable();
</personaldetails>

In both those cases the select does the same job of electing column names (Forename, Surname), changing column types(Sex) or just mirroring the column as is (dob). The extention method will manage the types for you but beware of nullable types. You may have to add extra handling in for nulls.

Alternatively you can just use .Select() or select i and all of the columns will be mirrored in the Datatable "as-is".


PS:
I can't tell you how much I love extention methods in .NET4.0. I even come up with the most petty extentions just cos I can :)

public static bool AsBool(this string boolString)
{    return boolString.Equals("T");    }