Convert select new to DataTable?

I am using .NET 3.5 and need to convert the below select new result into a DataTable. Is there something built in for this or anyone know of a method that can do this?

var contentList = (from item in this.GetData().Cast<IContent>()
                  select new
                  {
                      Title = item.GetMetaData("Title"),
                      Street = item.GetMetaData("Street"),
                      City = item.GetMetaData("City"),
                      Country = item.GetMetaData("Country")
                  });

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Easy and straightforward thing to do is to use reflection:

var records = (from item in this.GetData().Cast<IContent>()
                           select new
                           {
                               Title = "1",
                               Street = "2",
                               City = "3",
                               Country = "4"
                           });
var firstRecord = records.First();
if (firstRecord == null)
    return;

var infos = firstRecord.GetType().GetProperties();
DataTable table = new DataTable();
foreach (var info in infos) {
    DataColumn column = new DataColumn(info.Name, info.PropertyType);
    table.Columns.Add(column);
}

foreach (var record in records) {
    DataRow row = table.NewRow();
    for (int i = 0; i < table.Columns.Count; i++)
        row[i] = infos[i].GetValue(record);
    table.Rows.Add(row);
}

Code may not be working up front but should give you a general idea. First, you get propertyInfos from anonymous type and use this metadata to create datatable schema (fill columns). Then you use those infos to get values from every object.

Method 2

Here is one generic solution without Reflecting over the properties. Have an extension method as below

    public static DataTable ConvertToDataTable<TSource>(this IEnumerable<TSource>
                     records, params Expression<Func<TSource, object>>[] columns)
    {
        var firstRecord = records.First();
        if (firstRecord == null)
            return null;

        DataTable table = new DataTable();

        List<Func<TSource, object>> functions = new List<Func<TSource, object>>();
        foreach (var col in columns)
        {
            DataColumn column = new DataColumn();
            column.Caption = (col.Body as MemberExpression).Member.Name;
            var function = col.Compile();
            column.DataType = function(firstRecord).GetType();
            functions.Add(function);
            table.Columns.Add(column);
        }

        foreach (var record in records)
        {
            DataRow row = table.NewRow();
            int i = 0;
            foreach (var function in functions)
            {
                row[i++] = function((record));
            }
            table.Rows.Add(row);
        }
        return table;
    }

And Invoke the same using where parameters will be the column name in the order you want.

var table = records.ConvertToDataTable(
                                        item => item.Title, 
                                        item => item.Street, 
                                        item => item.City
                                      );

Method 3

You can convert your list result to datatable by the below function

   public static DataTable ToDataTable<T>(IEnumerable<T> values)
    {
        DataTable table = new DataTable();

        foreach (T value in values)
        {
            if (table.Columns.Count == 0)
            {
                foreach (var p in value.GetType().GetProperties())
                {
                    table.Columns.Add(p.Name);
                }
            }

            DataRow dr = table.NewRow();
            foreach (var p in value.GetType().GetProperties())
            {
                dr[p.Name] = p.GetValue(value, null) + "";

            }
            table.Rows.Add(dr);
        }

        return table;
    }

Method 4

There is a CopyToDataTable extension method which does that for you. It lives in System.Data.DataSetExtensions.dll

Method 5

Try this:

// Create your datatable.

DataTable dt = new DataTable();
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Street", typeof(double));


// get a list of object arrays corresponding
// to the objects listed in the columns
// in the datatable above.
var result = from item in in this.GetData().Cast<IContent>()             
             select dt.LoadDataRow(
                new object[] { Title = item.GetMetaData("Title"),
                              Street = item.GetMetaData("Street"),
                 },
                false);


// the end result will be a set of DataRow objects that have been
// loaded into the DataTable.

Original Article for code sample : Converting Anonymous type generated by LINQ to a DataTable type

EDIT: Generic Pseudocode:

void LinqToDatatable(string[] columns, Type[] datatypes, linqSource)
{
     for loop
     {
       dt.columns.add(columns[i], datatypes[i]);
     }

//Still thinking how to make this generic.. 
var result = from item in in this.GetData().Cast<IContent>()             
             select dt.LoadDataRow(
                new object[] { string[0] = item.GetMetaData[string[0]],
                               string[1] = item.GetMetaData[srring[1]
                 },
                false);


}

Method 6

   public static DataTable ListToDataTable<T>(this IList<T> data)
        {
            DataTable dt = new DataTable();
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                dt.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T t in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(t);
                }
                dt.Rows.Add(values);
            }
            return dt;
        }

After you do your select new you can to .ToList().ListToDataTable(). This uses ComponentModel reflection and is (theroetically) faster than System.Reflection.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0