Joining two data-table and save result in data-table

Question

I have 2 datasets, I want to join two datasets and save the result in a datatable.

I am able to join the tables but while saving the result in datatable it is throwing an error 'Unable to cast object of type d__38 4[System.Data.DataRow,System.Data.DataRow,System.String,<>f__AnonymousType0 7[System.String,System.String,System.Int32,System.Int32,System.String,System.String,System.String]]' to type 'System.Data.DataTable'

My Code:

private DataSet joindatasets(DataSet ds1, DataSet dsHeader)
        {
            DataTable dt; 
            DataSet ds = new DataSet(); 
            DataTable dt1=new DataTable();

        dt1.Columns.Add("PracticeCode", typeof(string));
        dt1.Columns.Add("PracticeName", typeof(string));
        dt1.Columns.Add("High", typeof(int));
        dt1.Columns.Add("Medium", typeof(int));
        dt1.Columns.Add("username", typeof(string));
        dt1.Columns.Add("MIAlertHeader", typeof(string));
        dt1.Columns.Add("MIAlertFooter", typeof(string));

        if (dsHeader.Tables.Count > 0)
        {
            for (int i = 0; i <= dsHeader.Tables.Count - 1; i++)
            {
                dt = dsHeader.Tables[i];

                var query = from table1 in ds1.Tables[0].AsEnumerable()
                             join table2 in dt.AsEnumerable()
                             on (string)table1["PracticeCode"] equals (string)table2["PracticeCode"]


                            select new
                            {
                                PracticeCode = (string)table1["PracticeCode"],
                                PracticeName = (string)table1["PracticeName"],
                                High = (int)table1["High"],
                                Medium = (int)table1["Medium"],
                                username = (string)table2["username"],
                                MIAlertHeader = (string)table2["MIAlertHeader"],
                                MIAlertFooter = (string)table2["MIAlertFooter"]
                            };
                dt1 = (DataTable)query;

            }
        }

        return ds;
    }

Please Help

They contain a common column PracticeCode on which I am applying inner joining, rest of the columns are different — Rahul Aggarwal, Mar 25 '19 at 08:12
Possible duplicate of [Convert select new to DataTable?](https://stackoverflow.com/questions/9937573/convert-select-new-to-datatable) — Tetsuya Yamamoto, Mar 25 '19 at 10:00

score 1 · Accepted Answer · answered Mar 25 '19 at 09:35

Such of error quite often happens when you try to convert IEnumerable<object> to DataRow. To work-around this you have to use DataTable.LoadDataRow method + CopyToDataTable method

            dt1 = (from table1 in ds1.Tables[0].AsEnumerable()
                         join table2 in dt.AsEnumerable()
                         on (string)table1["PracticeCode"] equals (string)table2["PracticeCode"]
                        select dt1.LoadDataRow(new object[]
                        {
                            (string)table1["PracticeCode"],
                            (string)table1["PracticeName"],
                            (int)table1["High"],
                            (int)table1["Medium"],
                            (string)table2["username"],
                            (string)table2["MIAlertHeader"],
                            (string)table2["MIAlertFooter"]
                        }, false)).CopyToDataTable();

More details, you'll find here: Creating a DataTable From a Query (LINQ to DataSet)

score 0 · Answer 2 · answered Mar 25 '19 at 10:20

Create the following method and pass any type of object your LINQ query returning.

public static DataTable CreateDataTableFromAnyCollection<T>(IEnumerable<T> list)
    {
        Type type = typeof(T);
        var properties = type.GetProperties();

        DataTable dataTable = new DataTable();
        foreach (PropertyInfo info in properties)
        {
            dataTable.Columns.Add(new DataColumn(info.Name, Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType));
        }

        foreach (T entity in list)
        {
            object[] values = new object[properties.Length];
            for (int i = 0; i < properties.Length; i++)
            {
                values[i] = properties[i].GetValue(entity,null);
            }

            dataTable.Rows.Add(values);
        }

        return dataTable;
    }

and pass your LINQ result like

DataTable dt = CreateDataTableFromAnyCollection(list);

FYI: This is well beyond the scope required for this question, but I've extended your method to recursively evaluate types which contain complex properties, including collections. You can see [my implementation](https://stackoverflow.com/questions/72049096/generic-method-to-flatten-a-collection-of-nested-objects-into-a-datatable/72095768#72095768) on the question, "[Generic Method to Flatten a Collection of Nested Objects into a DataTable?](https://stackoverflow.com/q/72049096/3025856)". This dramatically complicates the implementation, but may be useful for readers with more complex models. — Jeremy Caney, May 07 '22 at 23:09

Joining two data-table and save result in data-table

2 Answers2