Converting LINQ Join query to DataTable

Question

I want to convert the LINQ Join query result into the DataTable for which I have checked but couldn't found any authentic solution.

I have tried to do it two ways 1:

var query = from A in ABC.AsEnumerable()
                        join X in XYZ.AsEnumerable()
                        on A.Field<int>("A-ID") equals
                        X.Field<int>("X-ID")
                        where ((A.Field<string>("A-Column1") == "1")
                        && (r.Field<string>("X-Column1") == "1"))
                        select new
                        {
                            UserName = ABC.Field<string>("UserName"),
                            UserExperience = XYZ.Field<string>("UserExperience"),
                            ...
                        };

2:

DataTable dt = new DataTable();
            dt.Columns.Add("UserName", typeof(string));
            dt.Columns.Add("UserExperience", typeof(string));

var query = from A in ABC.AsEnumerable()
                        join X in XYZ.AsEnumerable()
                        on A.Field<int>("A-ID") equals
                        X.Field<int>("X-ID")
                        where ((A.Field<string>("A-Column1") == "1")
                        && (r.Field<string>("X-Column1") == "1"))
                        select dt.LoadDataRow(new object[]
                        {
                            ABC.Field<string>("UserName"),
                            XYZ.Field<string>("UserExperience"),
                            ...
                        }, false);

    DataTable ddt = new DataTable();

    ddt = query.CopyToDataTable();

But that both couldn't resolve my problem.

I want to convert that result into the DataTable / DataSet.

Thanks in advance.

score 0 · Accepted Answer · answered Jan 15 '19 at 07:30

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

Hope this will help you.

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:05

Converting LINQ Join query to DataTable

1 Answers1

Linked