How to merge two datatables on a common column in c# and fill 0's in the second table where they don't match?

Question

I am pretty new to c#, I have two datatables A and B which I would like to join / merge on a common column.

A:

id    | colA
1     | 30
------|------
2     | 20
------|------
3     | 10

B:

id    | colB
1     | 30
------|------
2     | 20

I would like to join / merge table B on A and get the result:

result:

id    | colA |colB
1     | 30   | 10
------|------|------
2     | 20   | 20
------|------|------
3     | 10   | 0   <-- fill 0's

Can someone please provide a reference to a previously similar question or a simple explanation? Thank you!

Answer 1

Try following :

           DataTable dtA = new DataTable("A");

            dtA.Columns.Add("id", typeof(int));
            dtA.Columns.Add("colA", typeof(int));

            dtA.Rows.Add(new object[] { 1,30 });
            dtA.Rows.Add(new object[] { 2,20 });
            dtA.Rows.Add(new object[] { 3,10 });

            DataTable dtB = new DataTable("B");

            dtB.Columns.Add("id", typeof(int));
            dtB.Columns.Add("colB", typeof(int));

            dtB.Rows.Add(new object[] { 1, 30 });
            dtB.Rows.Add(new object[] { 2, 20 });

            DataTable dtC = new DataTable("C");
            dtC = dtA.Copy();

            dtC.Columns.Add("colB");
            dtC.Columns["colB"].DefaultValue = 0;

            foreach (DataRow row in dtC.AsEnumerable())
            {
                row["colB"] = dtB.AsEnumerable().Where(x => x.Field<int>("id") == row.Field<int>("id")).Sum(x => x.Field<int>("colB"));
            }