none
Merge 2 datatable from LINQ RRS feed

  • Question

  • Hi, I have 2 datatable.

    1 to show Operator ID (TANTOU) , TOTAL SUM of DURATION and COUNT of event happened.
    Another 1 is to show Operator ID (TANTOU), AVERAGE of DURATION and COUNT of event happened.

    I have use LINQ function to get the result.

    But how can i combine it so that i can show both result in the same datagrid?
    (means, I just use 1 LINQ function that can display Operator ID, SUM, AVERAGE, and COUNT)

    here is the code of sum:

    Dim group As IEnumerable(Of DataRow) = _
                    From row In Ds.Tables(0).AsEnumerable() _
                        Group row By TANTOU = row.Field(Of String)("TANTOU") Into grp = Group _
                        Order By (TANTOU >= Convert.ToString(TANTOU)) _
                Select DTRow(grpDT, _
                                     New Object() {TANTOU, _
                                                   New TimeSpan( _
                                                    grp.Sum(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks _
                                                                ) _
                                                              ), _
                grp.Count() _
                                                             } _
                                                            )
    
     




    And here is the code of average:

    Dim group As IEnumerable(Of DataRow) = _
                    From row In Ds.Tables(0).AsEnumerable() _
                        Group row By TANTOU = row.Field(Of String)("TANTOU") Into grp = Group _
                        Order By (TANTOU >= Convert.ToString(TANTOU)) _
                Select DTRow(grpDT, _
                   New Object() {TANTOU, _
                                 New TimeSpan(CType( _
                                    grp.Average(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks),  _
                                    Int64 _
                                    ) _
                                    ), _
                                    grp.Count() _
                                    } _
                                    )
    

    Thank you :)

    Tuesday, November 27, 2012 2:57 PM

Answers

  • I already solved this problem after few testing since last night. Yeay! (^__^)

    Anyway, here is the solution:

       '''''' Start Join 2 Datatable ''''''
        
                    Dim JoinResult = _
                    From o In grpDT.AsEnumerable _
                    Join a In grpTot.AsEnumerable _
                    On a.Field(Of String)("Tantou") Equals o.Field(Of String)("Tantou") _
                  Select New With _
                  { _
                    Key .operatorID = a.Field(Of String)("Tantou"), _
                    Key .totalCount = o.Field(Of Int32)("Count"), _
                    Key .TotalSum = a.Field(Of TimeSpan)("Duration"), _
                    Key .Average = o.Field(Of TimeSpan)("Duration") _
                  }
        
                    
                    DataGridView1.DataSource = JoinResult

     

    • Marked as answer by lulu79 Wednesday, November 28, 2012 3:12 AM
    Wednesday, November 28, 2012 3:12 AM

All replies

  • Please check out the blow example.

    DataTable TestTable3 = new System.Data.DataTable();
    TestTable3.Columns.Add("OperatorID", typeof(int));
    TestTable3.Columns.Add("TOTALSUM", typeof(int));
    TestTable3.Columns.Add("COUNT", typeof(string));
    TestTable3.Rows.Add(1, 10,2);
    TestTable3.Rows.Add(2, 12,3);
    TestTable3.Rows.Add(3, 13,6);
    TestTable3.Rows.Add(4, 18,7);
    DataTable TestTable4 = new System.Data.DataTable();
    TestTable4.Columns.Add("OperatorID", typeof(int));
    TestTable4.Columns.Add("AVERAGE", typeof(int));
    TestTable4.Columns.Add("COUNT", typeof(string));
    TestTable4.Rows.Add(1, 15, 9);
    TestTable4.Rows.Add(2, 14, 1);
    TestTable4.Rows.Add(3, 16, 4);
    TestTable4.Rows.Add(4, 19, 5);
    var JoinResult = from n in TestTable3.AsEnumerable()
    join o in TestTable4.AsEnumerable() on n["OperatorID"] equals o["OperatorID"]
       select new 
       {
            operatorID = n["OperatorID"],
            TotalSum = n["TOTALSUM"],
            Average = o["AVERAGE"]
       };
    dataGridView1.DataSource = JoinResult.ToList();


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    Tuesday, November 27, 2012 5:48 PM
  • Thank you Sambath for your reply.

    But do you have an example in vb.net?

    Wednesday, November 28, 2012 12:46 AM
  • I already solved this problem after few testing since last night. Yeay! (^__^)

    Anyway, here is the solution:

       '''''' Start Join 2 Datatable ''''''
        
                    Dim JoinResult = _
                    From o In grpDT.AsEnumerable _
                    Join a In grpTot.AsEnumerable _
                    On a.Field(Of String)("Tantou") Equals o.Field(Of String)("Tantou") _
                  Select New With _
                  { _
                    Key .operatorID = a.Field(Of String)("Tantou"), _
                    Key .totalCount = o.Field(Of Int32)("Count"), _
                    Key .TotalSum = a.Field(Of TimeSpan)("Duration"), _
                    Key .Average = o.Field(Of TimeSpan)("Duration") _
                  }
        
                    
                    DataGridView1.DataSource = JoinResult

     

    • Marked as answer by lulu79 Wednesday, November 28, 2012 3:12 AM
    Wednesday, November 28, 2012 3:12 AM