Joining two DataTables in C#

Question

I have the following two data tables (DataTable) that represents PC allocations to students for different timeslots:

Table1:

Timeslot   PC1   PC2  PC3
1          A           B
2          A           B
3                 D
4                 D

Table2:

Timeslot   PC1   PC2  PC3
1                 C         
2                 C  
3
4

Would it be possible to join these two DataTables together into one DataTable, as follows?

Wanted:

Timeslot   PC1   PC2  PC3
1          A      C    B
2          A      C    B
3                 D
4                 D

I know that, in SQL, it would be quite straight forward left join, but I have no clue how I can do this in C# with DataTables.

Possible duplicate https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp — Hameed Syed, Feb 11 '18 at 03:42

score 2 · Answer 1 · answered Feb 11 '18 at 06:27

2

A bit shorter and more efficient if the result is merged in one of the source tables:

Table1.Rows.Cast<DataRow>().Join(Table2.Rows.Cast<DataRow>(), a => a[0], b => b[0], 
   (a, b) => {a[1] += "" + b[1]; a[2] += "" + b[2]; a[3] += "" + b[3]; return a; }).Count();

answered Feb 11 '18 at 06:27

Slai

22,144
5
45
53

I like it. The return a to change the original row is blowing my mind... I've never seen that before. I believe the count function is just to force it to execute, right? Very nice. – Obie Feb 11 '18 at 18:16
@Obie yes sure, Count/Last/Max etc. can be used to execute the query instead of foreach loop. The return is only because it has to return something and doesn't really matter what is returned. – Slai Feb 11 '18 at 18:20
Ahh yes, got it. Thanks! – Obie Feb 11 '18 at 18:33

score 0 · Answer 2 · answered Feb 11 '18 at 04:07

Here it is using LINQ:

var joinData = t1.Select().Join(t2.Select(), j1 => j1["TimeSlot"], j2 => j2["TimeSlot"],
            (j1, j2) => new { j1, j2 }).Select(j => new
            {
                TimeSlot = j.j1["TimeSlot"],
                PC1 = (string)j.j1["PC1"] != "" ? j.j1["PC1"] : j.j2["PC1"],
                PC2 = (string)j.j1["PC2"] != "" ? j.j1["PC2"] : j.j2["PC2"],
                PC3 = (string)j.j1["PC3"] != "" ? j.j1["PC3"] : j.j2["PC3"]
            });
        var resultTable = new DataTable();
        addColumns(resultTable);
        foreach(var row in joinData)
        {
            var dr = resultTable.NewRow().ItemArray = new object[] { row.TimeSlot, row.PC1, row.PC2, row.PC3 };
            resultTable.Rows.Add(dr);
            WriteLine($"{dr[0]} {dr[1]} {dr[2]} {dr[3]}");
        }

Joining two DataTables in C#

2 Answers2