Gabriel Bogéa Perez

When you query a DataTable using Linq the normal result will be an IEnumerable, which is not a DataTable (obviously). If you need a DataTable as a result you already have an extension method called CopyToDataTable which will turn your IEnumerable into a new DataTable. Although if you want to join two DataTables and the merge the DataRows into a new Anonymous Type the CopyToDataTable will not help you. Let’s see how to create a helper method that will transform an IEnumerable of anonymous types into a new DataTable.

First let’s create two DataTables: Person and Job. Here’s the code:

DataTable dtPerson;
DataTable dtJob;

dtPerson= new DataTable("Person");
DataColumn dc;

dc = new DataColumn("Id", typeof(int));
dtPerson.Columns.Add(dc);

dc = new DataColumn("Name", typeof(string));
dtPerson.Columns.Add(dc);

dc = new DataColumn("Age", typeof(int));
dtPerson.Columns.Add(dc);

//JOB DataTable
dtJob = new DataTable("Job");

dc = new DataColumn("PersonId", typeof(int));
dtJob.Columns.Add(dc);

dc = new DataColumn("Position", typeof(string));
dtJob.Columns.Add(dc);

Here is the code to populate the DataTables:

DataRow dr = dtPerson.NewRow();
dr[0] = 1;
dr[1] = "Gabriel";
dr[2] = 31;
dtPerson.Rows.Add(dr);

dr = dtPerson.NewRow();
dr[0] = 2;
dr[1] = "Elisa";
dr[2] = 27;
dtPerson.Rows.Add(dr);

dr = dtJob.NewRow();
dr[0] = 1;
dr[1] = "Programmer";
dtJob.Rows.Add(dr);

dr = dtJob.NewRow();
dr[0] = 2;
dr[1] = "Manager";
dtJob.Rows.Add(dr);

Now let’s write a Linq query to join these two DataTables:

var query = from p in dtPerson.AsEnumerable()
             join j in dtJob.AsEnumerable() on p.Field<int>("Id") equals j.Field<int>("PersonId")
             select new
             {
                 Id = p.Field<int>("Id"),
                 Name = p.Field<string>("Name"),
                 Job = j.Field<string>("Position")
             };

Notice that the result of the query is an IEnumerable of an anonymous type with three properties (Id, Name and Position) which is a merge of our two DataTables data. Now for the helper method that will convert this result to a new DataTable. The base of this idea is in my previous article on using reflection on anonymous types. I’ll use this principle to inspect the properties of the anonymous type and create new DataColumns for the new DataTable. After that all it’s needed is iterating over the results creating new DataRows. Here’s the method:

public static DataTable ToDataTable(this IEnumerable objectList)
{
    //if the result is null or if the number of
    //objects is less then 1, return null
    if (objectList == null || 
        objectList.OfType<object>().Count() < 1)
    {
        return null;
    }
    //create a new list based on the IEnumerable
    List<object> list = new List<object>(objectList.OfType<object>());

    //take the first object in the list
    object o = list[0];
    //get the type of the object
    Type t = o.GetType();
    //read all the info of the properties
    PropertyInfo[] properties = t.GetProperties();

    DataTable dt = new DataTable();
    //for each property create a new column
    //in the DataTable
    foreach (var pi in properties)
    {
        //the new column has the name of the property
        //and it's type
        DataColumn dc = new DataColumn(pi.Name, pi.PropertyType);
        //add the column to the DataTable
        dt.Columns.Add(dc);
    }

    //add the rows to the DataTable
    foreach (var item in list)
    {
        DataRow dr = dt.NewRow();
        //each property represents a column 
        //that has to be set
        foreach (var pi in properties)
        {
            dr[pi.Name] = pi.GetValue(item, null);
        }
        dt.Rows.Add(dr);
    }
    return dt;
}

That’s it. The returning DataTable will have three columns (int Id, string Name, string Position). If you want you can download the code for the class here. In this code for download I transformed the ToDataTable method into an extension method.