Dan Neely Dan Neely - 4 months ago 43
C# Question

Load result of Linq-to-DataSet query with join into datatable

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

Answer

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.