Use C# LINQ for DataTable connection

Inner joins combine lines from two DataTables based on common keys. This is useful for tasks such as merging information from different tables or filtering data based on specific conditions.

Suppose we have two DataTables, T1 and T2, the fields are as follows:

• T1(CustID, ColX, ColY)
• T2(CustID, ColZ)

We need to create a joint table TJ with the following fields:

• TJ (CustID, ColX, ColY, ColZ)

Using LINQ, we can perform intra-joining on the CustID column:

var results = from table1 in dt1.AsEnumerable()
              join table2 in dt2.AsEnumerable() on (int)table1["CustID"] equals (int)table2["CustID"]
              select new
              {
                  CustID = (int)table1["CustID"],
                  ColX = (int)table1["ColX"],
                  ColY = (int)table1["ColY"],
                  ColZ = (int)table2["ColZ"]
              };

This query generates a sequence of anonymous objects containing the connected columns. We can then iterate over the results and output them to the console:

foreach (var item in results)
{
    Console.WriteLine(String.Format("ID = {0}, ColX = {1}, ColY = {2}, ColZ = {3}", item.CustID, item.ColX, item.ColY, item.ColZ));
}

This code will produce the following output:

ID = 1, ColX = 11, ColY = 21, ColZ = 31
ID = 2, ColX = 12, ColY = 22, ColZ = 32
ID = 3, ColX = 13, ColY = 23, ColZ = 33
ID = 4, ColX = 14, ColY = 24, ColZ = 34
ID = 5, ColX = 15, ColY = 25, ColZ = 35