DataTable Linq join many columns

Question

I have a trouble with Linq Join. I want to join 2 tables, which they have same structure with n-columns. My problem is i don't know the names of those columns, so how can i rewrite those 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: Here is the same like Table 1 but it has some parameters in Comment, Attribute and the Rest.

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

Result: I would like to have joined Table like this

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

My Code would 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 out, dont 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;

Why don't you just return tb1 ? Instead of 'select new { ... }' do smth like 'select tb1' — Madman, Oct 23 '12 at 11:42
no, select tb1 won't do anything here. Firstly i lose the result from table2 and it shows me nothing at all — Uni Le, Oct 23 '12 at 11:47
Just a question, why do you need to merge them? Table2 seems to contain entire Table1? You could have a look at this question if it helps you http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables. What about IDs, how are they supposed to be handled in case of conflict? Conflicting data etc.. — flindeberg, Oct 23 '12 at 11:54
I want to join them because i want to have ID, Name and LastName like in Table 1. In Table 2 sometimes ID,LastName, Name could be missing. That's why i use outer left join — Uni Le, Oct 23 '12 at 12:03
If you convert the Linq result to a datatable you can select by column index. See http://stackoverflow.com/questions/2361852/how-to-access-columns-by-index-in-linq — Paul D'Ambra, Oct 23 '12 at 12:06

Risky Martin · Answer 1 · 2012-10-23T15:31:12.467

1

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

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's DataSource
 var result = table2.Clone();
 foreach(var row in rows)
 {
     result.Rows.Add(row.ItemArray);
 }
 dataGridView.DataSource = result;

edited Oct 23 '12 at 15:31

answered Oct 23 '12 at 14:17

Risky Martin

2,491
2
15
16

thanks Risky, very good idea, but then how can it be showed in DataGridView? I just got ID, Name, LastName and Row Columns, which Row Column has System.Data.Row parameters. How can i expand it? – Uni Le Oct 23 '12 at 14:47
@UniLe: You can populate a DataTable with the new DataRows and assign it to the DataGridView's DataSource. I'm not sure how you would create an anonymous object from a DataRow. – Risky Martin Oct 23 '12 at 15:06
Risky: I got Error This row already belongs to another table. At result.Rows.Add(row); – Uni Le Oct 23 '12 at 15:10
@UniLe: You are very much welcome! But now that I think about it, your latest example is pretty close to working and is much shorter. I'm going to try to get it to work. – Risky Martin Oct 23 '12 at 17:07
to reduce duplicates i would do var `rows = item.Select(x=>x.Row).Distinct();` 1 question more, if I have in table 1 1 more object and try to bind with table 2, which doesn't have this object, it doesn't show up, why is that? can i transfort your code so that i got left outer join? – Uni Le Oct 24 '12 at 08:29
OK i lose the problem by using the second code, work like charm – Uni Le Oct 24 '12 at 12:55

Risky Martin · Accepted Answer · 2012-10-24T14:01:06.573

1

For each row1 in table1, if there is a matching row2 in table2, 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;

edited Oct 24 '12 at 14:01

answered Oct 23 '12 at 17:20

Risky Martin

2,491
2
15
16

Risky: I got Error cannot apply indexing with [] to an expression type object row1 – Uni Le Oct 24 '12 at 07:10
Risky: OK i know, instead of `var row1` I should use `DataRow row1` thanks tousend more for the help... works great – Uni Le Oct 24 '12 at 12:55

score 1 · Answer 3 · answered Jun 02 '14 at 11:52

Try this, no need of using 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();

DataTable Linq join many columns

3 Answers3

Linked