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

回答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.

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

回答3:

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

回答4:

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


}

回答5:

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

回答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.