I cannot seem to get past the anonymous type used by Linq's select clause when using join. I want to put my query result into a datatable. Here's what I have in memory at runtime...
DataTable1 (source)
----------
col1A <int> (acting PK, not explicitly defined as such)
col1B <string>
col1C <string>
col1D <string> (FK -> dt2.col2A, also not explicitly defined)
col1E <string>
DataTable2 (source)
----------
col2A <string> (acting PK, also not explicitly defined)
col2B <string>
DataTable3 (destination)
----------
colA <int>
colB <string>
colC <string>
colD <string>
Here's what I want (SQL equivelant to what I'm looking for)...
INSERT INTO DataTable3 (colA, colB, colC, colD)
SELECT dt1.col1A, dt1.col1B, dt1.col1C, dt2.col2B
FROM DataTable1 dt1
LEFT JOIN DataTable2 dt2 ON (dt1.col1D = dt2.col2A)
Working backwards, I see this simple line to place an enumerated query result set into a datatable:
DataTable DataTable3 = query.CopyToDataTable();
Here's what I tried... I don't know how to build 'query' in a way that will allow me to use this. Attempt 1 (using var query; this worked, read further):
var query =
from dt1 in DataTable1.AsEnumerable()
join dt2 in DataTable2.AsEnumerable()
on dt1.Field<string>("col1D") equals dt2.Field<string>("col2A")
select new { col1A = dt1.Field<int>("col1A"),
col1B = dt1.Field<string>("col1B"),
col1C = dt1.Field<string>("col1C"),
col2B = dt2.Field<string>("col2B") };
But that gives me this error on 'query.CopyToDataTable()':
"Error 1 The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable(System.Collections.Generic.IEnumerable)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'."
From what I can surmise, this error is because 'select new' doesn't build DataRows into 'query', which .CopyToDataTable() demands; it just builds anonymous records. Furthermore, I don't know how to just cast query elements into DataRow because "it is inaccessible due to protection level".
Attempt 2 (using IEnumerable query; don't bother trying this):
IEnumerable<DataRow> query =
from dt1 in DataTable1.AsEnumerable()
join dt2 in DataTable2.AsEnumerable()
on dt1.Field<string>("col1D") equals dt2.Field<string>("col2A")
select new { col1A = dt1.Field<int>("col1A"),
col1B = dt1.Field<string>("col1B"),
col1C = dt1.Field<string>("col1C"),
col2B = dt2.Field<string>("col2B") };
This gives an error on 'join':
"Error 5 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)"
On a side note, if I comment out the 'join' clause (and the corresponding dt2 column in the 'select'), I get this error on the 'select':
"Error 2 Cannot implicitly convert type 'System.Data.EnumerableRowCollection' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)"
I also tried pre-defining DataTable3 with columns to give the 'select' clause a real DataRow to go off of, but I still couldn't get the 'select' to take advantage of it.
Edits, Details, Solution: Thanks Tim for the help. I went with Attempt 1 (var query). I also pre-defined DataTable3 as such:
DataTable DataTable3 = new DataTable();
DataTable3.Columns.Add("colA", System.Type.GetType("System.Int32"));
DataTable3.Columns.Add("colB", System.Type.GetType("System.String"));
DataTable3.Columns.Add("colC", System.Type.GetType("System.String"));
DataTable3.Columns.Add("colD", System.Type.GetType("System.String"));
I stored the results from 'query' using a foreach loop as Tim suggested:
foreach (var x in query)
{
DataRow newRow = DataTable3.NewRow();
newRow.SetField("colA", x.col1A);
newRow.SetField("colB", x.col1B);
newRow.SetField("colC", x.col1C);
newRow.SetField("colD", x.col2B);
DataTable3.Rows.Add(newRow);
}
This could be genericized in a few ways with some additional effort, but this was good enough for my purposes.
Linq should be used for queries only. Use it to query the source and build your anonymous types. Then use a foreach
to add the DataRows
. In general: you cannot use CopyToDataTable
for anything else than IEnumerable<DataRow>
. That's the reason for the error on the anonymous type.
( There is a workaround using reflection that i wouldn't suggest in general:
Implement CopyToDataTable<T>
Where the Generic Type T
Is Not a DataRow
)
Imho a foreach
is the best you can do since it's very readable:
var query =
from dt1 in DataTable1.AsEnumerable()
join dt2 in DataTable2.AsEnumerable()
on dt1.Field<string>("col1D") equals dt2.Field<string>("col2A")
select new
{
col1A = dt1.Field<int>("col1A"),
col1B = dt1.Field<string>("col1B"),
col1C = dt1.Field<string>("col1C"),
col2B = dt2.Field<string>("col2B")
};
foreach (var x in query)
{
DataRow newRow = DataTable3.Rows.Add();
newRow.SetField("col1A", x.col1A);
newRow.SetField("col1B", x.col1B);
newRow.SetField("col1C", x.col1C);
newRow.SetField("col2B", x.col2B);
}