Inner Join of Datatables in C#

Inner join of DataTables in C#

If you are allowed to use LINQ, take a look at the following example. It creates two DataTables with integer columns, fills them with some records, join them using LINQ query and outputs them to Console.

    DataTable dt1 = new DataTable();
    dt1.Columns.Add("CustID", typeof(int));
    dt1.Columns.Add("ColX", typeof(int));
    dt1.Columns.Add("ColY", typeof(int));

    DataTable dt2 = new DataTable();
    dt2.Columns.Add("CustID", typeof(int));
    dt2.Columns.Add("ColZ", typeof(int));

    for (int i = 1; i <= 5; i++)
    {
        DataRow row = dt1.NewRow();
        row["CustID"] = i;
        row["ColX"] = 10 + i;
        row["ColY"] = 20 + i;
        dt1.Rows.Add(row);

        row = dt2.NewRow();
        row["CustID"] = i;
        row["ColZ"] = 30 + i;
        dt2.Rows.Add(row);
    }

    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"]
                 };
    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));
    }
    Console.ReadLine();

// 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

How to join two DataTable together with inner join

 DataTable dt1 = new DataTable();
    dt1.Columns.Add("CustID", typeof(int));
    dt1.Columns.Add("ColX", typeof(int));
    dt1.Columns.Add("ColY", typeof(int));

    DataTable dt2 = new DataTable();
    dt2.Columns.Add("CustID", typeof(int));
    dt2.Columns.Add("ColZ", typeof(int));

    for (int i = 1; i <= 5; i++)
    {
        DataRow row = dt1.NewRow();
        row["CustID"] = i;
        row["ColX"] = 10 + i;
        row["ColY"] = 20 + i;
        dt1.Rows.Add(row);

        row = dt2.NewRow();
        row["CustID"] = i;
        row["ColZ"] = 30 + i;
        dt2.Rows.Add(row);
    }

    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"]
                 };
    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));
    }
    Console.ReadLine();

// 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

join two datatable and return the result in another data table

The problem is that the query expression (LINQ) returns an IEnumerable of the anonymous type you created, instead of a DataTable. IMHO, I prefer working with collections instead of datatables, but if you want to have a DataTable, you can create and populate it yourself from the collection, like this:

var collection = from t1 in dt1.AsEnumerable()
                 join t2 in dt2.AsEnumerable()
                    on t1["crsnum"] equals t2["crsnum"]
                 select new { Name = t1["crsname"], Group = t2["group"] }; 
DataTable result = new DataTable("NameGroups");
result.Columns.Add("crsname", typeof(string));
result.Columns.Add("group", typeof(int));

foreach (var item in collection)
{
   result.Rows.Add(item.Name, item.Group);
}

for more details, and a more general solution, take a look at this.

Use .Select().Join() to Join Two DataTables

I typically accomplish this by building an anonymous object that contains a reference to my source and destination objects through a Join or GroupJoin, then looping over the result of the Join to update my destination object. See the example below.

Take a look at the documentation on Join and GroupJoin. Join is great for a 1-1 match, while GroupJoin is a 0-* match (like a SQL left join). The arguments to Join and GroupJoin allow you to specify a selector function for each IEnumerable followed by a selector function for the output object. Note that t1 and t2 below refer to table1 and table2.

using System;
using System.Data;
using System.Linq;

public class Program
{
    public static void Main()
    {
        var table1 = GetEmptyTable();
        table1.Rows.Add(1, "Old Value", false);
        table1.Rows.Add(2, "Untouched Value", false);

        var table2 = GetEmptyTable();
        table2.Rows.Add(1, "New Value", false);
        table2.Rows.Add(3, "Unused Value", false);

        Console.WriteLine("Before...");
        Console.WriteLine(PrintTable(table1));

        var matched = table1.Select()
            .Join(table2.Select(), t1 => (int)t1["A"], t2 => (int)t2["A"], (t1, t2) 
=> new
            {
                DestinationRow = t1,
                SourceRow = t2
            });
        foreach (var match in matched)
        {
            match.DestinationRow["B"] = match.SourceRow["B"];
            match.DestinationRow["C"] = true;
        }

        Console.WriteLine("After...");
        Console.WriteLine(PrintTable(table1));
    }

    private static DataTable GetEmptyTable()
    {
        var table = new DataTable();
        table.Columns.Add("A", typeof(int));
        table.Columns.Add("B", typeof(string));
        table.Columns.Add("C", typeof(bool));
        return table;
    }

    private static string PrintTable(DataTable table)
    {
        return string.Join(Environment.NewLine, table.Select().Select(x => "[" + 
string.Join(", ", x.ItemArray) + "]"));
    }
}

C# DataTable Inner join with dynamic columns

I found a solution which doesn't rely on looping through the columns.

It uses the 'Merge' method, which I had previously dismissed as I thought both tables required the same structure.

First you need to create a primary key on the two data-tables:

// set primary key
T1.PrimaryKey = new DataColumn[] { T1.Columns["DateStamp"] };
T2.PrimaryKey = new DataColumn[] { T2.Columns["DateStamp"] };

Then add both tables to a data-set so a relationship can be added:

// add both data-tables to data-set
DataSet dsContainer = new DataSet();
dsContainer.Tables.Add(T1);
dsContainer.Tables.Add(T2);

Next add the relationship between the two key columns in the data-set:

// add a relationship between the two timestamp columns
DataRelation relDateStamp = new DataRelation("Date", new DataColumn[] { T1.Columns["DateStamp"] }, new DataColumn[] { T2.Columns["DateStamp"] });
dsContainer.Relations.Add(relDateStamp);

Finally you can now copy the first data-table into a new 'combined' version, and then merge in the second:

// populate combined data
DataTable dtCombined = new DataTable();
dtCombined = T1.Copy();
dtCombined.Merge(T2, false, MissingSchemaAction.Add);

Note: The Merge method requires the second argument to be false or else it copies the structure but not the data of the second table.

This would then combine the following tables:

T1 (2012-05-09, 111, 222)
T2 (2012-05-09, 333, 444, 555)

into a combined version based on the primary-key:

J1 (2012-05-09, 111, 222, 333, 444, 555)

Join 3 datatables in c#

You can do like this create a class like below

public class TestClass
{
    public int ScheduleID { get; set; }
    public string Name { get; set; }
    public string Details { get; set; }
    public int ScheduleGroupID { get; set; }
    public string Description { get; set; }
}

And write the query like this

List<TestClass> colection = (from x in db.Table1
                join y in db.Table2 on x.ScheduleID  equals y.ScheduleID
                join z in db.Table3 on y.ScheduleGroupID equals z.ScheduleGroupID
                select new CityClass
                {
                   ScheduleID = x.ScheduleID,
                   Name = x.Name,
                   Details  = x.Details,
                   ScheduleGroupID = y.ScheduleGroupID,
                   Description = z.Description
                }).ToList();

How to do a LINQ join that behaves exactly like a physical database inner join?

Earlier Soution ...

public static DataTable JoinDataTables2(DataTable dt1, DataTable dt2, string table1KeyField, string table2KeyField) {
   DataTable result = ( from dataRows1 in dt1.AsEnumerable()
                            join dataRows2 in dt2.AsEnumerable()
                            on dataRows1.Field<string>(table1KeyField) equals dataRows2.Field<string>(table2KeyField) 
                            select new {Col1= datarows1Field<string>(table1FieldName), Col2= datarows2.Field<string>(table2FieldName)}).Distinct().CopyToDataTable();
   return result;
}

You can list all the columns from table1 in select query. Following query has per-defined DataTable with all columns from table1 and just key column from table2. It may help you.

public static DataTable JoinDataTables2(DataTable dt1, DataTable dt2, string table1KeyField, string table2KeyField)
{
    DataTable joinTable = new DataTable();
    foreach (DataColumn dt1Column in dt1.Columns)
    {
        joinTable.Columns.Add(dt1Column.ColumnName, dt1Column.DataType);
    }

    var col2 = dt2.Columns[table2KeyField];
    joinTable.Columns.Add(col2.ColumnName,typeof(string));

    var result = (from dataRows1 in dt1.AsEnumerable()
                  join dataRows2 in dt2.AsEnumerable()
                      on dataRows1.Field<string>(table1KeyField) equals dataRows2.Field<string>(table2KeyField)
                  select new
                  {
                      Col1 = dataRows1,
                      Col2 = dataRows2.Field<string>(table2KeyField)
                  });
    foreach (var row in result)
    {
        DataRow dr = joinTable.NewRow();
        foreach (DataColumn dt1Column in dt1.Columns)
        {
            dr[dt1Column.ColumnName] = row.Col1[dt1Column.ColumnName];
        }

        dr[table2KeyField] = row.Col2;
        joinTable.Rows.Add(dr);
    }
    joinTable.AcceptChanges();
    return joinTable.AsEnumerable().Distinct().CopyToDataTable();
}

Inner join using LINQ on DataTables

You missed brackets after AsEnumerable, so it's treated as Method Group, not IEnumerable<DataRow>:

var customerNames = from customers in customerTableDT.AsEnumerable()
                    join aliases in customerAliasesTableDT.AsEnumerable() on customers.Field<int>("CustomerID") equals aliases.Field<int>("CustomerID")
                    where aliases.Field<string>("Alias").Contains(iString) select customers.Field<string>("Name")

---

Related Topics