CODEWITHSUNDEEP
c#linqjoindatatable
Gazman Development Gosford
Gazman Development Gosford

Reputation: 97

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

I have been searching high and low for this to no avail. I have two DataTables that I want to join without creating a new resultant table as I simply need to update some rows in one of the tables to be displayed in a grid view, similar to the below code, but with a join:

sage_invoices.Select("CCE2 IS NULL")
                .ToList<DataRow>()
                .ForEach(row =>
                {
                    row["Error"] = 1;
                    row["ErrorMessage"] = "Missing Region Code (Dimension 2 - CCE2)";
                });

Everything I've found produces a new output datatable, similar to the below code:

var collection = from t1 in dt1.AsEnumerable()
         join t2 in dt2.AsEnumerable()
         on t1["id"] equals t2["id"]
         select new { T1 = t1, T2 = t2 };

What I can't find is how to join two DataTables using .Join:

sage_invoices.Select()
                .Join(<What Goes here?>)
                .ToList<DataRow>()
                .ForEach(row =>
                {
                    row["Error"] = 1;
                    row["ErrorMessage"] = "ITMREF is not a Sage Product Code";
                });

If anyone could point me in the right direction, I would be most grateful.

Thanks Gareth

Upvotes: 0

Views: 3457

Answers (1)

Tyler
Tyler

Reputation: 2400

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) + "]"));
    }
}

Upvotes: 2

Related Questions