How to merge DataTables but combine rows if ID matches?

Question

Input:

Say I have two DataTables. The first one:

ID  Date    ValueA  ValueB
1   Nov.21  72.4    23.4
1   Nov.22  71.4    24.4
2   Nov.21  74.4    21.4

The second one:

ID  Date    ValueC  ValueD
1   Nov.21  42.4    53.4
1   Nov.22  41.4    54.4
2   Nov.21  44.4    51.4

What I tried:

I tried to do DataTable1.Merge(DataTable2);, but I get this:

ID  Date    ValueA  ValueB  ValueC  ValueD
1   Nov.21  72.4    23.4    
1   Nov.22  71.4    24.4    
2   Nov.21  74.4    21.4    
1   Nov.21                  42.4    53.4
1   Nov.22                  41.4    54.4
2   Nov.21                  44.4    51.4

Desired result:

I'd like to combine rows, if their ID and Date match.

I want this:

ID  Date    ValueA  ValueB  ValueC  ValueD
1   Nov.21  72.4    23.4    42.4    53.4
1   Nov.22  71.4    24.4    41.4    54.4
2   Nov.21  74.4    21.4    44.4    51.4

Answer 1

You can use LINQ to join the two DataTables based on ID and Date and then project/Load the results into a resultant DataTable using LoadDataRow method like:

DataTable dtResult = new DataTable();

var query = from row1 in dt1.AsEnumerable()
            join row2 in dt2.AsEnumerable() on
                new { ID = row1.Field<int>("ID"), Date = row1.Field<DateTime>("Date") }
                equals
                new { ID = row2.Field<int>("ID"), Date = row2.Field<DateTime>("Date") }
            select dtResult.LoadDataRow(new object[]
    {
        row1.Field<int>("ID"),
        row1.Field<DateTime>("Date"),
        row1.Field<double>("ValueA"),
        row1.Field<double>("ValueB"),
        row2.Field<double>("ValueC"),
        row2.Field<double>("ValueD")
    }, false);

Make sure that the types specified with Field extension method matches with that of your columns in DataTable.