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.

you need [Full Outer JOIN using LINQ](http://stackoverflow.com/questions/5489987/linq-full-outer-join) — Habib, Aug 28 '14 at 15:13
I'd go a for loop and a dictionary. If you really want LINQ, check this out: [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq). The key is `.DefaultIfEmpty()`, you need to do LEFT OUTER JOIN and then RIGHT OUTER JOIN, so that gives you a FULL JOIN. — Victor Zakharov, Aug 28 '14 at 15:19
[Perform a Left Outer Join by Using the Group Join Clause](http://msdn.microsoft.com/en-us/library/bb918093.aspx) — Tim Schmelter, Aug 28 '14 at 15:23
have you tried to apply the solution to your datatables? is it working? — Vland, Aug 29 '14 at 09:29
Thanks for your answer! I will try it on monday, we have holiday today and I have no access to the database. No worries I will not forget this question! — ruedi, Aug 29 '14 at 14:05

score 0 · Accepted Answer · answered Aug 28 '14 at 18:20

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

Hi! Your code runs perfect. I was still looking for a left/right->fulljoin solution. Thanks for your help! — ruedi, Sep 09 '14 at 16:04
I dont care about the stupid points. You got them. It is just, all commentators above lead me to a full join solution (follow the links). I thought... ok, try it out. Btw. I dont get points for check my own answer, but I get points for checking your answer. — ruedi, Sep 09 '14 at 17:01
@ruedi Ok sorry I didn't know that. The commentators gave hints but no actual solutions. The problem with your query is the halved values so I don't think that you can make a group join before reducing the values to 50% (not in a single query at least). this is why is a very interesting question imho — Vland, Sep 09 '14 at 17:09

score 0 · Answer 2 · answered Sep 09 '14 at 16:02

I got it now. My Solution is:

Dim qLeft = From a in dt1.Getdt1Info
                Group Join b in dt2.Getdt2Info
                On a.FirstName + a.LastName Equals b.FirstName + b.LastName  
                Into NewGroup = Group
                From c in NewGroup.DefaultIfEmpty
                Select New With{
                .Name = a.FirstName + ", " + a.LastName,
                .Value1 = a.Val1 + If (c is nothing, 0D, c.Val1) * 0.5,
                .Value2 = a.Val2 + If (c is nothing, 0D, c.Val2) * 0.5      
                }

Dim qRight =From a in dt2.Getdt2Info
                Group Join b in dt1.Getdt1Info  
                On a.FirstName + a.LastName Equals b.FirstName + b.LastName
                Into NewGroup = Group
                From c in NewGroup.DefaultIfEmpty
                Select New With{
                .Name = a.FirstName + ", " + a.LastName,
                .Value1 = a.val1 * 0.5 + If (c Is Nothing, 0D, c.Val1),
                .Value2 = a.val2 * 0.5 + If (c Is Nothing, 0d, c.Val2)}

Dim qFull = qleft.Concat(qRight).GroupBy(function(x) x.Name).Select(function(x) x.First)

I still dont know why qleft.Union(qRight) doesnt eliminate the duplicates. I solved that with the functions (s. Code).

Add up values from two datatables into a new datatable

2 Answers2