How to do a Full Outer Join of 2 or more Datatables in C# with a common column

Question

I need to merge/join datatables on C# with a common column.

I am aware of many examples and the questions on the same topic that have been asked. I have not found any that answer my question though.

Below is the code i am using.

The code only allows for one common column based on the data of on datatable. I need a common column, but it needs to consider any other "Account#" may be used in another datatable and add it into the common column.

Also the code only allows for merging of 2 datatables. i need to merge 31 datatables, to merge 1 datatable for each day of the month.

I have a datatable for each day of the month, dt_docDAY01, dt_docDAY02, dt_docDAY03 etc.

each datatable contains an account number "Account#" and a balance stored in a column refering to the day "DAY01", "DAY02", etc.

Can you tell me how to change the code so that i will include all accounts from all tables.

Also, how i would merge all the datatables in this code, so i dont have to run this same code 31 times.

' string id = "Account#";

                var tableJoinedDAY02 = dt_docDAY01_GROUPED.Clone(); // create columns from table1

                // add columns from table2 except id
                foreach (DataColumn column in dt_docDAY02_GROUPED.Columns)
                {
                    if (column.ColumnName != id)
                        tableJoinedDAY02.Columns.Add(column.ColumnName, column.DataType);
                }

                tableJoinedDAY02.BeginLoadData();

                foreach (DataRow row1 in dt_docDAY01_GROUPED.Rows)
                {
                    foreach (DataRow row2 in dt_docDAY02_GROUPED.Rows)
                    {
                        if (row1.Field<string>(id) == row2.Field<string>(id))
                        {
                            var list = row1.ItemArray.ToList(); // items from table1

                           // add items from table2 except id
                            foreach (DataColumn column in dt_docDAY02_GROUPED.Columns)
                                if (column.ColumnName != id)
                                    list.Add(row2[column]);

                            tableJoinedDAY02.Rows.Add(list.ToArray());
                        }
                    }
                }

                    tableJoinedDAY02.EndLoadData();`

Table1

Account#    |    Day01
1234        |      11
4567        |      22
0909        |      33

Table2

Account#    |    Day02
1234        |      12
0909        |      34
5578        |      99
0065        |      34

Table3

Account#    |    Day03
1234        |      13
7777        |      44

Expected Outcome Merged Table

Table1

Account#    |    Day01     |   Day02     |   Day03
1234        |      11      |      12     |     13
4567        |      22      |       0     |     0
0909        |      33      |      34     |     0
5578        |      0       |      99     |     0
0065        |      0       |      34     |     0
7777        |      0       |      0      |     44

Answer 1

@Infost, you are trying to do what in SQL language is a full outer join. Searching that on SO pointed to this answer https://stackoverflow.com/a/16832096/97471 that I have adapted for more than 2 tables:

Starting from an MVCE like this one:

DataTable table1 = new DataTable();
table1.Columns.Add("Account", typeof(int));
table1.Columns.Add("Day01", typeof(decimal));

table1.Rows.Add(1234, 11);
table1.Rows.Add(4567, 22);
table1.Rows.Add(0909, 33);

DataTable table2 = new DataTable();
table2.Columns.Add("Account", typeof(int));
table2.Columns.Add("Day02", typeof(decimal));

table2.Rows.Add(1234, 12);
table2.Rows.Add(0909, 34);
table2.Rows.Add(5578, 99);
table2.Rows.Add(0065, 34);

DataTable table3 = new DataTable();
table3.Columns.Add("Account", typeof(int));
table3.Columns.Add("Day03", typeof(decimal));

table3.Rows.Add(1234, 13);
table3.Rows.Add(7777, 44);

You can join them calling the following function:

var table123 = FullOuterJoinDataTables(table1, table2, table3);

Here is the function source:

DataTable FullOuterJoinDataTables(params DataTable[] datatables) // supports as many datatables as you need.
{
    DataTable result = datatables.First().Clone();

    var commonColumns = result.Columns.OfType<DataColumn>();

    foreach (var dt in datatables.Skip(1))
    {
        commonColumns = commonColumns.Intersect(dt.Columns.OfType<DataColumn>(), new DataColumnComparer());
    }

    result.PrimaryKey = commonColumns.ToArray();

    foreach (var dt in datatables)
    {
        result.Merge(dt, false, MissingSchemaAction.AddWithKey);
    }

    return result;
}

/* also create this class */
public class DataColumnComparer : IEqualityComparer<DataColumn>
{
    public bool Equals(DataColumn x, DataColumn y) { return x.Caption == y.Caption; }

    public int GetHashCode(DataColumn obj) { return obj.Caption.GetHashCode(); }

}

The output is

Account Day01   Day02   Day03
1234    11      12      13
4567    22
909     33      34
5578            99
65              34
7777                    44

Answer 2

This need to handled as follows, all the tables cannot be joined together by magic, let's take a smaller sample set:

1. Table1 (dt1) - Account# | Day01
2. Table2 (dt2) - Account# | Day02
3. Table3 (dt3) - Account# | Day03
4. Table4 (dt4) - Account# | Day04

    dt1.AsEnumerable()
    .Join(dt2.AsEnumerable(), d1 => (int)d1["Account#"], d2 =>            
          (int)d2["Account#"],  
          (d1,d2) => new {Account = (int)d1["Account#"],Day01 =  
                     d1["Day01"],Day02 = d2["Day02"]})
    .Join(dt3.AsEnumerable(), d12 => d12.Account, d3 => (int)d3["Account#"],  
      (d12,d3) => new {d12.Account,d12.Day01,d12.Day02,Day03=d3["Day03"]})
    .Join(dt4.AsEnumerable(), dAll => dAll.Account, d4 =>   
          (int)d4["Account#"],  
          (dAll,d4) => new 
         {dAll.Account,dAll.Day01,dAll.Day02,dAll.Day03,Day04=d4["Day04"]})

Result of the above operation would be IEnumerable<AnonymousType>, where as of now Anonymous type consists of the Properties Account,Day01,Day02,Day03,Day04, similarly you can add up to Day31. Also note how post first join we start using the AnonymousType generated as part of the last Join statement

This needs conversion to DataTable, which would be something similar to the code posted in the following thread:

Convert IEnumerable to DataTable

Also check this as its for conversion to DataTable using IEnumerable of Anonymous type, using a Nuget utility Fastmember

C# how to convert IEnumerable anonymous lists into data table