Linq into clause returning only columns from joined table

Question

I joined a 2 data tables based on the EMP column and used copytodatatable, however when i load the new datatable into datagridview it only shows the joined columns(salarydt) from the query, why is this??

 var collection = from p in dt.AsEnumerable()
                         join q in salaryDT.AsEnumerable() on p.Field<int>("Emp") equals q.Field<int>("Emp1") into UP
                         from t in UP
                         select t;
 DataTable resultdt = new DataTable();
 dt = collection.CopyToDataTable();

@TimSchmelter what can i do to select all the columns from the join? i just wanted to join the salary column to the main datatable based on the EMP number but its giving me so many problems — wren, Jun 05 '19 at 15:56
Please check this question: https://stackoverflow.com/questions/1671765/select-all-columns-for-all-tables-in-join-linq-join — Flavio Francisco, Jun 05 '19 at 16:15
Your query only selects `t`. You need to combine the `p` and `t` objects to get the values of both. — Jeff Mercado, Jun 05 '19 at 16:22

Gert Arnold · Accepted Answer · 2019-06-05T19:21:36.227

2

DataTable itself is perfectly geared to merging itself with another data table, using its, well, Merge method. Here's a litte example:

var dt1 = new DataTable("a");
dt1.Columns.Add("ID", typeof(int));
dt1.Columns.Add("Item1", typeof(int));
dt1.PrimaryKey = new[] { dt1.Columns[0] };

var dt2 = new DataTable("a");
dt2.Columns.Add("ID", typeof(int));
dt2.Columns.Add("Item2", typeof(int));
dt2.PrimaryKey = new[] { dt2.Columns[0] };


for (int i = 0; i < 10; i++)
{
    dt1.Rows.Add(new object[] { i, i });
    dt2.Rows.Add(new object[] { i, i + 10 });
}

dt1.Merge(dt2);

Now dt1 has three columns, ID, Item1, and Item2.

ID  Item1   Item2
0   0       10
1   1       11
2   2       12
3   3       13
4   4       14
5   5       15
6   6       16
7   7       17
8   8       18
9   9       19

edited Jun 05 '19 at 19:21

answered Jun 05 '19 at 18:53

Gert Arnold

105,341
31
202
291

Thanks alot this is just what i wanted, a simple and short method, however my second datatable contains ID that is not in the first datatable and its returning me an error saying it failed to enable constraint(because C# still adds the unmatching ids into the merged table with null values for all the other columns), so i just looped through the merged table and removed the columns that are null and now it works perfect :) – wren Jun 06 '19 at 00:45
Oops meant to say removed rows where there are null values, anyway thanks again i cant believe i didnt explore this merge method after seeing it online while searching for solutions to my problem, spent a frustrating 2 hrs on my problem before asking on stack overflow – wren Jun 06 '19 at 01:02

Flavio Francisco · Answer 2 · 2019-06-05T18:19:09.027

1

Maybe what you should to do is just return a custom list of items:

Option 1

var data = new Employee[] { new Employee { Id = 1, Name = "John Doe" } };

var data2 = new Salary[] { new Salary { Id = 1, Wage = "$ 50,000.00" } };

var collection = from p in data
                   join q in data2 on p.Id equals q.Id              
                   select new { Id = p.Id, Name = p.Name, Wage = q.Wage };

Please check the example: link

Option 2

var data = new Employee[] { new Employee { Id = 1, Name = "John Doe" } };

var data2 = new Salary[] { new Salary { Id = 1, Wage = "$ 50,000.00" } };

var collection = from p in data
                   join q in data2 on p.Id equals q.Id              
                   select new { p, q };

edited Jun 05 '19 at 18:19

answered Jun 05 '19 at 16:18

Flavio Francisco

755
1
8
21

I tried option 1 but i have over 30 columns and its not practical to select all of them manually, and option 2 i tried as well but i cant copy to datatable afterwards – wren Jun 05 '19 at 18:22
Looks that there is no shortcut at least based on this documentation: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/creating-a-datatable-from-a-query-linq-to-dataset – Flavio Francisco Jun 05 '19 at 18:32

Linq into clause returning only columns from joined table

2 Answers2