CODEWITHSUNDEEP
c#linqienumerable
Dante
Dante

Reputation: 3316

Error when Copying Query from linq to Datatable

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.

Upvotes: 1

Views: 5816

Answers (3)

Brad Rem
Brad Rem

Reputation: 6026

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

Upvotes: 4

Sankara
Sankara

Reputation: 1479

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

Upvotes: 1

KeithS
KeithS

Reputation: 71573

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.

Upvotes: 3

Related Questions