Error when Copying Query from linq to Datatable

Question

I just started working on a project that requires Linq to Sql, and I have been able to make queries and retrieve data. But right now I need to fill a DataTable with the data I am retrieving.

My first code was the following:

MyDatabase db = new MyDatabase();
var query = from cust in db.Customers
            where cust.CustomerName != "Dante"
            orderby cust.CustomerName
            select new { Name = cust.CustomerName };

So, since I needed to copy the content of my query to a Datatable I tried this:

 IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
    select order;

// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();

Then, my code looks like this:

IEnumerable<DataRow> myQuery = from cust in db.Customers.AsEnumerable()
                    where cust.Name != "Dante"
                    orderby cust.Name
                    select new { Name = cust.Name };

DataTable myDataTable = myQuery.CopyToDataTable<DataRow>();

But with this code the compiler raises and error:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Data.DataRow>

The error is raised at the select word.

So, What am I doing wrong?? What can I do in order to avoid this conversion issue?

Hope someone can help me, thanks in advance.

score 4 · Accepted Answer · answered Sep 05 '12 at 23:40

There is a way to create a DataTable from a result other than IEnumerable<DataRow>, but it is rather involved. Implement CopyToDataTable Where the Generic Type T Is Not a DataRow.

For your case, though, I would recommend doing it the following way. Go back to your original query:

MyDatabase db = new MyDatabase();
var query = from cust in db.Customers
            where cust.CustomerName != "Dante"
            orderby cust.CustomerName
            select new { Name = cust.CustomerName };

Then define your single field DataTable because when you eventually create a DataRow, it needs a schema to work from:

DataTable myDataTable = new DataTable();
myDataTable.Columns.Add(
    new DataColumn()
    {
        DataType = System.Type.GetType("System.String"),
        ColumnName = "Name"
    }
);

And finally, go through the results of your query and manually add the DataRows to your DataTable.

foreach (var element in query)
{
    var row = myDataTable.NewRow();
    row["Name"] = element.Name;
    myDataTable.Rows.Add(row);
}

score 3 · Answer 2 · answered Sep 05 '12 at 20:58

The problem is exactly as the error states; your "select" clause is creating instances of an anonymous type with one member, "Name". This anonymous type is not and cannot be a DataRow, so the implicit conversion between what the query produces and the variable you want to set it into fails.

Instead, you should take this query, and for each element of the anonymous type that it returns, add the value as a new DataRow of the DataTable. You cannot just create a new DataRow from scratch; the DataRow class requires a context, namely the parent DataTable, to define the columns the DataRow should have.

Sankara · Answer 3 · 2012-09-05T21:19:58.417

1

This will do i guess

var myQuery = from cust in db.Customers.AsEnumerable()
                    where cust.Name != "Dante"
                    orderby cust.Name
                    select new { Name = cust.Name };

or try this

 string[] myQuery = db.Customers.Rows
                       .Cast<DataRow>()
                       .Where(r=>r.Name!="Dante")
                       .Orderby(r=>r.Name)
                       .Select(r=>r.Field<string>("Name"))
                       .ToArray()

edited Sep 05 '12 at 21:19

answered Sep 05 '12 at 20:57

Sankara

1,469
15
22

It is not useful to me, if I use `var` then `myQuery` object does not show a definition for the method `CopyToDataTable` – Dante Sep 05 '12 at 20:59
you can convert that myquery to array of DataRow. – Sankara Sep 05 '12 at 21:01
I appreciate your effort in the answer, my problem here is that, I need to select only one column of the entire row, so, your edited answer does not work for me since it returns an array of DataRows with all the columns of the `Customer` table – Dante Sep 05 '12 at 21:12
Oops i missed that I will add that – Sankara Sep 05 '12 at 21:13

Error when Copying Query from linq to Datatable

3 Answers3