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

@GerardoGrignoli thanks for the downvote, super helpful, not. — Infost, Aug 18 '16 at 04:55
How does that inner join example help me with the fact that i need to join more than 2 tables? — Infost, Aug 18 '16 at 05:09
You can join Table1 with Table2 into Table12. And then join Table12 with Table3 into Table123, and so on. — Gerardo Grignoli, Aug 18 '16 at 05:11
There is a catch. You are looking for outer join, not inner join. So you could use this one instead: http://stackoverflow.com/a/17705602/97471 — Gerardo Grignoli, Aug 18 '16 at 05:26

score 10 · Answer 1 · edited May 23 '17 at 12:01

10

@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

edited May 23 '17 at 12:01

Community

1
1

answered Aug 18 '16 at 06:12

Gerardo Grignoli

14,058
7
57
68

Gerardo, This looks perfect, i cant get the function compiling correctly though. – Infost Aug 18 '16 at 06:56
i have unexpected character on this line var table123 = FullOuterJoinDataTables(table1, table2, table3);. i had error in the class also, but it is fixed with your edit. – Infost Aug 18 '16 at 07:17
What .net framework are you using? What compilation error on which line? – Gerardo Grignoli Aug 18 '16 at 07:17
Gerardo, this code works brilliantly. I retyped the line that had the compile issue, and it works perfectly. thank you so much for your assistance. – Infost Aug 18 '16 at 11:43
Gerardo, it appears that 99% of the data merges correctly with this code. There is some data that comes across with a 0. The data is ok pre merge. Do you have any suggestions? For example amount is 2537.5 before merge 0 after merge. – Infost Aug 21 '16 at 03:04
I can't repro your problem out of thin air. Please provide an [MVCE](http://stackoverflow.com/help/mcve) – Gerardo Grignoli Aug 21 '16 at 15:09
Good conversion method – Mrinal Kamboj Aug 22 '16 at 05:07
maybe the common column is not exactly equal? (a space?) or one column exists one more than one DT with different column types? You need to generate an example code that fails so I cant try it – Gerardo Grignoli Sep 01 '16 at 15:27
@Infost, it is working fine to me. You need to create a stand alone project where you are reproducing the problem, so I can see how this code is failing and how to fix it. I doubt Microsoft's Merge method has a bug. This is probably related to your data or your datables schema and can be solved on your project. It is likely that the problem is not in the `FullOuterJoinDataTables` function. – Gerardo Grignoli Sep 01 '16 at 22:30

score 1 · Answer 2 · edited May 23 '17 at 12:01

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

Table1 (dt1) - Account# | Day01

Table2 (dt2) - Account# | Day02

Table3 (dt3) - Account# | Day03

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

Hi Mrinal, can you provide more info on how i get this list data back to a datatable? — Infost, Aug 22 '16 at 04:57

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

2 Answers2