error in binding datatable to database by linq

Question

i exacly copy from msdn but the following code gives me error

    The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method
'System.Data.DataTableExtensions.CopyToDataTable<T>   (System.Collections.Generic.IEnumerable<T>,
 System.Data.DataTable, System.Data.LoadOption)'.  There is no implicit reference conversion from
 'AnonymousType#1' to 'System.Data.DataRow'.

my code:

Item[] items = new Item[] 
                                {
                                    new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"}, 
                                  new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
                                  new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
                                  new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}
                                };

            // Create a table with a schema that matches that of the query results.            
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("Price", typeof(int));
            dt1.Columns.Add("Genre", typeof(string));

            var query = from i in items
                        where i.Price > 9.99
                        orderby i.Price
                        select new { i.Price, i.Genre };

            query.CopyToDataTable(dt1, LoadOption.PreserveChanges);

how to make it workabe?

Try to rename the extension(f.e. `CopyAnyToDataTable`), maybe that's a naming conflicht with the `DataTableExtensions.CopyToDataTable` extension method. — Tim Schmelter, Aug 28 '14 at 08:11
@TimSchmelter no the type argument has to be compatible with DataRow see the constraint on T: http://msdn.microsoft.com/en-us/library/bb396189(v=vs.110).aspx — Selman Genç, Aug 28 '14 at 08:12
@Selman22: i assume that OP is using Microsoft's `ObjectShredder` class which allows to use `CopyToDataTable` with any type. http://msdn.microsoft.com/en-us/library/bb669096(v=vs.110).aspx — Tim Schmelter, Aug 28 '14 at 08:16
i didn't get it.Anyone can explain with working code example. — decoder, Aug 28 '14 at 08:55

score 1 · Answer 1 · edited May 23 '17 at 12:13

I exacly copy from msdn...

Are you trying to implement Microsofts ObjectShredder<T>-class which allows to use CopyToDataTable with any type?

Then try to rename the extension(f.e. CopyAnyToDataTable), it could be a naming conflict with the DataTableExtensions.CopyToDataTable extension method which allows only DataRows.

Some time ago i also had that issue, here's a similar question:

Exception using CopyToDataTable with "new {..}" LINQ query

score 1 · Accepted Answer · answered Aug 28 '14 at 09:00

Try this code, place it in a static helper class and this will allow you to call ToDataTable(); on the items.

public static DataTable ToDataTable<T>(this IEnumerable<T> data)
    {
        DataTable dt = new DataTable();
        foreach (var prop in data.First().GetType().GetProperties())
        {
            dt.Columns.Add(prop.Name);
        }

        foreach (T entry in data)
        {
            List<object> newRow = new List<object>();
            foreach (DataColumn dc in dt.Columns)
            {
                var val = entry.GetType().GetProperty(dc.ColumnName).GetValue(entry, null);
                newRow.Add(val);
            }
            dt.Rows.Add(newRow.ToArray());
        }
        return dt;
    }

@decoder If you have the above in a separate static class, you can call i like so `DataTable dt1 = query.ToDataTable();` — Catwood, Aug 28 '14 at 09:17

error in binding datatable to database by linq

2 Answers2