DataTable Linq Combines Multiple Columns

Question

I'm having problems with Linq Join. I want to join two tables that have the same structure with n-columns. My problem is that I do not know the names of these columns, so how can I rewrite them in select new?

Table 1: Here I have some parameters in ID, Name and LastName. Comment, Attribute, and the rest are null

ID Name LastName Comment Attribute ... "what" "ABC" ... "hello" "SDE" ... 3 lola 1 de 4 miki ... ... ... ... 

Table 2: This is the same as in Table 1, but there are some parameters in the Comment, Attribute, and Rest parameters.

  ID Name LastName Comment Attribute ... "what" "ABC" ... "hello" "SDE" ... 1 de "hi" 4 miki "OKK" 3 lola "yo" "LL" 

Result: I would like to join a table like this

  ID Name LastName Comment Attribute ... "what" "ABC" ... "hello" "SDE" ... 3 lola "yo" "LL" 1 de "hi" 4 miki "OKK" ... ... ... ... ... ... 

My code will be:

 var Result= from tb1 in table1.AsEnumerable() join tb2 in tabl2.AsEnumerable() on new { Name = tb1.Field<String>("Name"), LastName = tb1.Field<String>("LastName"), } equals new { Name=tb2.Field<String>("Name"), LastName=tb2.Field<String>("LastName"), } into grp1 from tb3 in grp1.DefaultIfEmpty() select new { ID = tb1.Field<String>("ID"), Name = tb1.Field<String>("Name") , LastName = tb1.Field<String>("LastName"), Comment = tb3!= null ? tb3.Field<String>("Comment") : null, Attribute= tb3!= null ? tb3.Field<String>("Attribute") : null, ... // Here should be next Columns Name but don't know how to put there }; 

---

I tried with this code, but my compiler just hanged myself, I don't know why

  for (int i = 2; i < table1.Rows.Count; i++) { foreach (DataRow dr in table2.Rows) { if ((table1.Rows[i]["Name"].ToString() == dr["Name"].ToString())&&table1.Rows[i]["LastName"].ToString() == dr["LastName"].ToString()) { table1.Rows.RemoveAt(i); table1.ImportRow(dr); } } } dataGridView1.DataSource = table1; 

Answer 1

For each row1 in table 1, if there is a corresponding row2 in table 2, use row2. Otherwise, use row1.

 var newTable = table1.Clone(); foreach (DataRow row1 in table1.Rows) // To skip the first 2, use table1.Rows.Cast<DataRow>().Skip(2) { var row = table2.Rows.Cast<DataRow>().FirstOrDefault(row2 => row1["Name"].ToString() == row2["Name"].ToString() && row1["LastName"].ToString() == row2["LastName"].ToString()) ?? row1; newTable.ImportRow(row); } dataGridView1.DataSource = newTable; 

Answer 2

How about joining, as you did, and then copying three known fields from the rows of table1 to the rows of table2?

 var copiedTable2 = table2.Copy(); // Copy table2 if you don't want it to be modified var items = from tb1 in table1.AsEnumerable() join tb2 in copiedTable2.AsEnumerable() on new { Name = tb1.Field<String>("Name"), LastName = tb1.Field<String>("LastName"), } equals new { Name=tb2.Field<String>("Name"), LastName=tb2.Field<String>("LastName"), } into grp1 from tb3 in grp1.DefaultIfEmpty() select new { ID = tb1.Field<String>("ID"), Name = tb1.Field<String>("Name") , LastName = tb1.Field<String>("LastName"), Row = tb3 ?? table2.NewRow(); }; foreach(var item in items) { item.Row.SetField<String>("ID", item.ID); item.Row.SetField<String>("Name", item.Name); item.Row.SetField<String>("LastName", item.LastName); } var rows = items.Select(x => x.Row); // How to set the rows as a DataGridView DataSource var result = table2.Clone(); foreach(var row in rows) { result.Rows.Add(row.ItemArray); } dataGridView.DataSource = result; 

Answer 3

Try this, no need to use loops

 var resultTable = from tb1 in table1.AsEnumerable() join tb2 in tabl2.AsEnumerable() on tb1["Name"].ToString() equals tb2["Name"].ToString() where tb1["LastName"].ToString() == tb2["LastName"].ToString() select r; DataTable resultTable =result.CopyToDataTable();