Add up values from two datatables into a new datatable

Question

I have two strongly typed Datatable (dt1):

|FirstName|LastName|Val1|Val2|
|Tony     |Stark   |34  |35  |
|Steve    |Rogers  |12  |23  |
|Natasha  |Romanoff|2   |100 |

and the second (dt2)

|FirstName|LastName|Val1|Val2|
|Tony     |Stark   |16  |5   |
|Bruce    |Banner  |2   |1   |
|Steve    |Rogers  |54  |40  |

I try to create a new Datatable where I add up the values for the persons. I need a outer join since I need all persons and the value in the second table is halved.

So the result should looks like:

|FirstName|LastName|Val1|Val2|
|Tony     |Stark   |42  |37.5|
|Steve    |Rogers  |39  |43  |
|Natasha  |Romanoff|2   |100 |
|Bruce    |Banner  |1   |0.5 |

My approach was with LINQ:

Dim query = 
from a in ds1.Table1
Join b in ds2.Table2
On a.FirstName + a.LastName Equals b.FirstName + b.Lastname
Select New With {
.FirstName = a.FirstName,
.LastName = a.LastName,
.Val1 = a.Val1 + b.Val1 *0.5,
.Val2 = a.Val2 + b.Val2 *0.5
}

But I dont get all persons with the approach. I also tried

Dim query = 
From a in ds1.Table1
From b in ds2.Table2
Select New With{
Key .KeyName = a.FirstName + a.LastName = b.FirstName + b.FirstName,
.Val1 = a.Val1 + b.Val1 *0.5,
.Val2 = a.Val2 + b.Val2 * 0.5
}

Now I get many entries for each person. Could anyone help me get this done. I dont know if there is maybe another approach without Linq to solve this.

Answer 1

An example using group by. The thing to consider is that before performing the group by and the Sum operation all the values in the second table must be divided by 2. I take care of that before the .Concat

var dt1 = new List<MyClass>();
dt1.Add(new MyClass { FirstName = "Tony", LastName = "Stark", Val1 = 34, Val2 = 35});
dt1.Add(new MyClass { FirstName = "Steve", LastName = "Rogers", Val1 = 12, Val2 = 23});
dt1.Add(new MyClass { FirstName = "Natasha", LastName = "Romanoff", Val1 = 2, Val2 = 100 });


var dt2 = new List<MyClass>();
dt2.Add(new MyClass { FirstName = "Tony", LastName = "Stark", Val1 = 16, Val2 = 5 });
dt2.Add(new MyClass { FirstName = "Bruce", LastName = "Banner", Val1 = 2, Val2 = 1 });
dt2.Add(new MyClass { FirstName = "Steve", LastName = "Rogers", Val1 = 54, Val2 = 40 });


var q = from a in dt1
                .Concat(
                    from b in dt2 
                    select new MyClass 
                        {
                            FirstName = b.FirstName,
                            LastName = b.LastName,
                            Val1 = b.Val1 * 0.5m,
                            Val2 = b.Val2 * 0.5m
                        })
        group a by new {a.FirstName, a.LastName}
        into g
        select new
            {
                g.First().FirstName,
                g.First().LastName,
                Val1 = g.Sum(x => x.Val1),
                Val2 = g.Sum(x => x.Val2),
            };


foreach (var s in q)
{
    Console.WriteLine("{0} {1} {2} {3}", s.FirstName,s.LastName,s.Val1,s.Val2);
}

Result

Tony Stark 42,0 37,5
Steve Rogers 39,0 43,0
Natasha Romanoff 2 100
Bruce Banner 1,0 0,5