CODEWITHSUNDEEP
c#asp.netlinqdatatable
decoder
decoder

Reputation: 926

error in binding datatable to database by linq

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?

Upvotes: 0

Views: 790

Answers (2)

Catwood
Catwood

Reputation: 157

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

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460028

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

Upvotes: 1

Related Questions