c# - Merge columns of two DataTables using linq

Question

I have two DataTables: dt1 and dt2.

dt1:

ID     | Name     | Address | QTY
-------+----------+---------+-----
A1     | Dog      | C1      | 272    
A2     | Cat      | C3      | 235    
A3     | Chicken  | C2      | 254    
A4     | Mouse    | C4      | 259 
A5     | Pig      | C5      | 233 

dt2:

ID     | Name     | Address | QTY MAX
-------+----------+---------+--------
A1     | Dog      | C1      | 250    
A2     | Cat      | C3      | 200    
A3     | Chicken  | C2      | 300    
A6     | Rabbit   | C6      | 350    

But, I want to merge dt1 and dt2 to dt3 like below:

ID     | Name     | Address | QTY   | QTY MAX
-------+----------+---------+-------+--------
A1     | Dog      | C1      | 272   | 250
A2     | Cat      | C3      | 235   | 200
A3     | Chicken  | C2      | 254   | 300
A4     | Mouse    | C4      | 259   | 0
A5     | Pig      | C5      | 233   | 0
A6     | Rabbit   | C6      | 0     | 350

Can any one help me?

Answer 1

This solution is not a linq solution as you could simply use DataTable.Merge & DataTable.PrimaryKey to get the desired output.

Here is a dummy example which you can use:

var dt1 = new DataTable();
var p1 = dt1.Columns.Add("a", typeof(int)); //Use this to add Primary Key constraint
dt1.Columns.Add("b");
dt1.Columns.Add("c");
dt1.Rows.Add("1", "apple", "10");
dt1.Rows.Add("2", "mango", "20");
dt1.Rows.Add("3", "orange", "30");
dt1.Rows.Add("4", "banana", "40");
dt1.PrimaryKey = new DataColumn[] { p1 }; //This removes duplication of rows

var dt2 = new DataTable();
var p2 = dt2.Columns.Add("a", typeof(int)); //Use this to add Primary Key constraint        
dt2.Columns.Add("b");
dt2.Columns.Add("d");
dt2.Rows.Add("1", "apple", "50");
dt2.Rows.Add("2", "mango", "60");
dt2.Rows.Add("3", "orange", "70");
dt2.Rows.Add("5", "grapes", "80");
dt2.PrimaryKey = new DataColumn[] { p2 }; //This removes duplication of rows

var dt3 = dt1.Copy();
dt3.Merge(dt2);  // Merge here merges the values from both provided DataTables

Taking your question into consideration:

var dt1 = new DataTable();
var p1 = dt1.Columns.Add("ID", typeof(string));
dt1.Columns.Add("Name", typeof(string));
dt1.Columns.Add("Address", typeof(string));
dt1.Columns.Add("Qty", typeof(int));
dt1.Columns["Qty"].DefaultValue = 0; //Setting default value
dt1.Rows.Add("A1", "Dog", "C1", 100);
dt1.Rows.Add("A2", "Cat", "C3", 200);
dt1.Rows.Add("A3", "Chicken", "C2", 300);
dt1.Rows.Add("A4", "Mouse", "C4", 400);
dt1.Rows.Add("A5", "Pig", "C5", 500);
dt1.PrimaryKey = new DataColumn[] { p1 };

var dt2 = new DataTable();
var p2 = dt2.Columns.Add("ID", typeof(string));
dt2.Columns.Add("Name", typeof(string));
dt2.Columns.Add("Address", typeof(string));
dt2.Columns.Add("Qty Max", typeof(int));
dt2.Columns["Qty Max"].DefaultValue = 0; //Setting default value
dt2.Rows.Add("A1", "Dog", "C1", 600);
dt2.Rows.Add("A2", "Cat", "C3", 700);
dt2.Rows.Add("A3", "Chicken", "C2", 800);
dt2.Rows.Add("A6", "Rabbit", "C6", 900);
dt2.PrimaryKey = new DataColumn[] { p2 };

var dt3 = dt1.Copy();
dt3.Merge(dt2);

Output:

---

Thanks @shA.t for suggesting to include DataColumn.DefaultValue so that blank cells could be replaced with 0. Also his answer seems to include linq features which I guess is what you are looking for!