I've the following two exampled DataTables which shows data loaded from a DB:
table1: table2:
| pk |groupID|cField|field1|field2| | pk |groupID|cField|field1|field2|
|----|-------|------|------|------| |----|-------|------|------|------|
| 1 | 1 | 0.5 | 10 | $5 | | 10 | 1 | 0.5 | 20 | $10 |
| 2 | 1 | 1 | 10 | $10 | | 11 | 1 | 1 | 20 | $20 |
| 3 | 1 | 2 | 10 | $20 | | 12 | 1 | 2 | 20 | $40 |
| 4 | 1 | 5 | 10 | $50 | | 13 | 1 | 5 | 20 | $100 |
The expected result would be (dtAux):
|groupID|cField|t1_field1|t1_field2|t2_field1|t2_field2|
|-------|------|---------|---------|---------|---------|
| 1 | 0.5 | 10 | $5 | 20 | $10 |
| 1 | 1 | 10 | $10 | 20 | $20 |
| 1 | 2 | 10 | $20 | 20 | $40 |
| 1 | 5 | 10 | $50 | 20 | $100 |
Based on this other post, I've tried the following code:
dtAux = new DataTable();
dtAux.Columns.Add("groupID");
dtAux.Columns.Add("cField");
dtAux.Columns.Add("t1_field1");
dtAux.Columns.Add("t1_field2");
dtAux.Columns.Add("t2_field1");
dtAux.Columns.Add("t2_field2");
IEnumerable<DataRow> qry = from t1 in table1.AsEnumerable() join t2 in table2.AsEnumerable()
on t1.Field<float>("cField") equals t2.Field<float>("cField")
where t1.Field<int>("groupID") == 1
select dtAux.LoadDataRow(new Object[] {
t1.Field<int>("groupID"), t1.Field<float>("cField"),
t1.Field<int>("field1"), t1.Field<decimal>("field2"),
t2.Field<int>("field1"), t2.Field<decimal>("field2")
}, false);
Yet, it returns an empty table. Any hints or help is greatly appreciated.
The qry is not executed. You need .CopyToDataTable() to execute it.
qry.CopyToDataTable();