views:

778

answers:

2

I have a linq to dataset query that joins two tables and extracts the desired parameters from each. I need to get them into a DataTable to bind to a DataGridView. The example I found for doing this on MSDN is a trivial example taking a single value from a single table, but when I tried to change my query to follow it I was unable to do so. The CopyToDataTable() method requires that the query be assigned to a IEnumerable<DataRow>, but when I do so I'm told that an explict cast is needed; but the cast fails at runtime with the exception:

Unable to cast object of type 'd__61\4[System.Data.DataRow,System.Data.DataRow,System.Int32,<>f__AnonymousType0\1[System.Int32]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

Original working query:

var query = MyDataSet.Table1.AsEnumerable().Join(MyDataSet.Table2.AsEnumerable(),
 table1 => table1.Field<Int32>("Table1_Id"),
 table2 => table2.Field<Int32>("Table1_Id"),
 (table1, table2) => new
 {
  Table1ID = table1.Field<Int32>("Table1_Id")
  //Other parameters commented out to simplify the example
 });

Non-working query with explicit cast:

IEnumerable<DataRow> query = (IEnumerable<DataRow>)MyDataSet.Table1.AsEnumerable().Join(MyDataSet.Table2.AsEnumerable(),
 table1 => table1.Field<Int32>("Table1_Id"),
 table2 => table2.Field<Int32>("Table1_Id"),
 (table1, table2) => new
 {
  Table1ID = table1.Field<Int32>("Table1_Id")
  //Other parameters commented out to simplify the example
 });
+1  A: 

Take at look at this link Creating a DataTable From a Query (LINQ to DataSet) (scroll down to the "Creating a Custom CopyToDataTable Method" section)

Vedran
If that is applicable it is certainly more elegant than my answer.
Henk Holterman
Thanks. I'll have to give that a try in the morning.
Dan Neely
Well that looks like it works, except it looks like the fix for nullable types in a comment isn't working for me and the cast between DBNull and MyType is failing. I'm not sure though because unless it's part of the bug itself there shouldn't be any actual Nulls in the data I'm testing with.
Dan Neely
Unless it's related to the problem mentioned above (which causes a constraints failed exception) the performance also is badly lacking. It takes about 4 seconds to process 10k rows in table1 vs 1.45 seconds to create the rows bypassing linq entirely and doing foreach (datarow row in table1)
Dan Neely
+1  A: 

In both cases, you are creating a new 'anonymous type' to store the results.

To make the second one work, you would need something like:

var query = ... => new DataRow() 
{
});

except that is not going to work because DataRow has no public constructor and cannot be initialised this way.

So, use the first one and iterate over the results (note that I'm guessing a little here, and that you have to setup the columns for table3 first):

foreach (var row in query)
{
   var r = table3.NewRow();
   r["Table1ID"] = row.Table1ID;
   r["Table2ID"] = row.Table1ID;                
}

Edit:

 var query = ...;  // step 1

 query = query.ToList();  // add this,  step 2

 foreach(...) { }  // step 3

If you time the 3 steps above separately you will probably see that step 2 takes the most time.

Henk Holterman
If not as elegant, your solution works and is about 3 times as fast as well.
Dan Neely
The breakdown of where the time goes is frustrating though. My 10k test set takes about .05 seconds to run the linq query, and 1.35s to convert the results into a DataTable.
Dan Neely
Dan, Linq has something called deferred execution. The foreach is actually fetching the records. I'll edit.
Henk Holterman
Ahhh. In that case it seems linq doesn't really add much. Running the query and creating a datatable from the results is only 3% faster than just foreaching over the datatable directly.
Dan Neely
I don't know what you where expecting from Linq here. It will rarely be faster than anything else, just more convenient.
Henk Holterman
I'd never used it before and was giving it a spin mostly to see what it was capable of, and if using it to process data in the backend would give any net benefits. Not being fluent in SQL and thus finding LINQ much harder to work with I'm thinking the answer is no.
Dan Neely