Convert LINQ to DataTable

Question

The linq query is returning result in form of {DataRow Job1, DataRow Run}. How can i convert JoinResult into a DataTable .

var JoinResult = (from Job1 in Jobdata.AsEnumerable()
                  join Product in Productdata.AsEnumerable()
                  on Job1.Field<int>("Job_Id") equals Product.Field<int>("Job_Id")
                  join Run in data.AsEnumerable()
                  on Job1.Field<int>("Run_Id") equals Run.Field<int>("Run_Id")
                  select new { Job1, Run });

Unclear (for example, do `Jobdata` and `Product` have data table relations, why don't you use the regular data table solutions?) and broad. Did you look for existing solutions? This isn't exactly a new problem. — Gert Arnold, Nov 25 '17 at 16:09
So you want a `DataTable` with the columns of each data row side-by-side? — D Stanley, Nov 27 '17 at 15:30

score 1 · Answer 1 · answered Nov 27 '17 at 15:22

You can create an extension method & use it like below:

    /// <Summary>
    /// Convert a IEnumerable to a DataTable.
    /// <TypeParam name="T">Type representing the type to convert.</TypeParam>
    /// <param name="source">List of requested type representing the values to convert.</param>
    /// <returns> returns a DataTable</returns>
    /// </Summary>
    public static DataTable ToDataTable<T>(this IEnumerable<T> source)
    {
        // Use reflection to get the properties for the type we’re converting to a DataTable.
        var props = typeof(T).GetProperties();

        // Build the structure of the DataTable by converting the PropertyInfo[] into DataColumn[] using property list
        // Add each DataColumn to the DataTable at one time with the AddRange method.
        var dt = new DataTable();
        dt.Columns.AddRange(
          props.Select(p => new DataColumn(p.Name, BaseType(p.PropertyType))).ToArray());

        // Populate the property values to the DataTable
        source.ToList().ForEach(
          i => dt.Rows.Add(props.Select(p => p.GetValue(i, null)).ToArray())
        );

        return dt;
    }


//To call the above method:
var dt = JoinResult.ToDataTable();

Note: You just need to update your linq query to get IEnumerable data. Hope it helps you.

Jonathan Applebaum · Answer 2 · 2017-11-25T16:13:31.897

You can create a new datatable by looping trough the query result:

private DataTable createDt()
{
    var JoinResult = (from Job1 in Jobdata.AsEnumerable()
                      join Product in Productdata.AsEnumerable()
                      on Job1.Field<int>("Job_Id") equals Product.Field<int>("Job_Id")
                      join Run in data.AsEnumerable()
                      on Job1.Field<int>("Run_Id") equals Run.Field<int>("Run_Id")
                      select new { Job1, Run });

    DataTable newdt = new DataTable();

    // declare strongly typed data columns
    DataColumn run = new DataColumn("run");
    run.DataType = System.Type.GetType("System.Int32");
    newdt.Columns.Add(run);

    DataColumn job1 = new DataColumn("job1");
    job1.DataType = System.Type.GetType("System.Int32");
    newdt.Columns.Add(job1);


    foreach (var x in JoinResult)
    {
        DataRow dr = newdt.NewRow();
        dr["run"] = x.Run;
        dr["job1"] = x.Job1;
        newdt.Rows.Add(dr);
    }

    return newdt;
}

BTW - you can not use CopyToDataTable() in your case: Why am I not getting .CopyToDataTable() in Linq Query()

but if you insist to use it: How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow

but as per this it should work : https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/creating-a-datatable-from-a-query-linq-to-dataset — Pranay Rana, Nov 25 '17 at 17:10

Convert LINQ to DataTable

2 Answers2