Joining tables using primary keys LINQ

Question

I'm trying to join tables by primary keys using the PrimaryKey method (the ID column is the primary key in both tables). But at the end I get an empty table. Tell me how to make the request correct?

clientDT:

ID	name	city
1	John	New York
2	Jacob	Los Angeles
3	William	Chicago
4	Ethan	San Francisco

orderDT:

ID	order_number
1	123547
2	568688
3	89779

As a result, I expect to see a table like this:

name	order_number	city
John	123547	New York
Jacob	568688	Los Angeles
William	89779	Chicago

My code:

var result = from client in clientDT.AsEnumerable()
             join orders in orderDT.AsEnumerable()
                  on clientDT.PrimaryKey equals orderDT.PrimaryKey
             select new 
                    {
                        Name = client.Field <string> ("name"),
                        City = client.Field <string> ("city"),
                        Order_number = orders.Field <string> ("order_number")                      
                      
                    };
            DataTable dt = new DataTable();
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("city", typeof(string));
            dt.Columns.Add("order_number", typeof(string));
            foreach(var row in result)
            {
                dt.Rows.Add(new object[] { row.Name, row.City, row.Order_number});
            }
            
result_DT  = dt;

Answer 1

Its actually quite simple. The problem is that you try to compare all primary keys.

The table.PrimaryKey property will return a collection of all columns that make up the key. So what you are doing is comparing if a List containing one column is equal to another List containing a column, which is false, since they contain different columns. You need to orient yourself along your created rows. So this is the best I can give you at the moment. Its not really pretty but it gets the job done

var result = from client in clientDT.AsEnumerable()
             join orders in orderDT.AsEnumerable()
                  on client[clientDT.PrimaryKey[0]] equals orders[orderDT.PrimaryKey[0]]
             select new
                    {
                        Name = client.Field<string>("name"),
                        City = client.Field<string>("city"),
                        Order_number = orders.Field<string>("order_number")
                    };

DataTable dt = new DataTable();
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("city", typeof(string));
dt.Columns.Add("order_number", typeof(string));

foreach (var row in result)
{
    dt.Rows.Add(new object[] { row.Name, row.City, row.Order_number });
}

The secret lies in this line

on client[clientDT.PrimaryKey[0]] equals orders[orderDT.PrimaryKey[0]]

To try and explain it: your client and your orders are rows. From these rows we can get the value of a column, in our case we need the value of the column containing the PK which in our case is just one column which makes things nice.

So our primary key column is orderDT.PrimaryKey[0] and to get the value of it in the individual rows during the request we do orders[orderDT.PrimaryKey[0]] and we are done.