Combining two datatable and getting the amount from second table by using linq

 

Questions


I have two tables as shown below.

Table 1

╔══════════════╦═════╦════╗
║     NAME     ║ AGE ║ ID ║
╠══════════════╬═════╬════╣
║ firstname 1  ║  20 ║  1 ║
║ firstname 2  ║  21 ║  2 ║
║ firstname 3  ║  22 ║  3 ║
║ firstname 4  ║  23 ║  4 ║
║ firstname 5  ║  24 ║  5 ║
║ firstname 6  ║  25 ║  6 ║
║ firstname 7  ║  26 ║  7 ║
║ firstname 8  ║  27 ║  8 ║
║ firstname 9  ║  28 ║  9 ║
║ firstname 10 ║  29 ║ 10 ║
╚══════════════╩═════╩════╝

Table 2

╔════════╦══════════╗
║ LINKID ║ QUANTITY ║
╠════════╬══════════╣
║      1 ║        1 ║
║      1 ║        1 ║
║      2 ║        1 ║
║      2 ║        1 ║
║      2 ║        1 ║
║      3 ║        1 ║
║      3 ║        1 ║
║      3 ║        1 ║
║      3 ║        1 ║
║      4 ║        1 ║
║      4 ║        1 ║
║      4 ║        1 ║
║      4 ║        1 ║
║      4 ║        1 ║
║      5 ║        1 ║
║      5 ║        1 ║
║      5 ║        1 ║
║      5 ║        1 ║
║      5 ║        1 ║
║      5 ║        1 ║
║      6 ║        1 ║
║      6 ║        1 ║
║      6 ║        1 ║
║      6 ║        1 ║
║      6 ║        1 ║
║      6 ║        1 ║
║      6 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      7 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      8 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║      9 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
║     10 ║        1 ║
╚════════╩══════════╝

the result table looks like this.

╔══════════════╦═════╦════╦═════╗
║     NAME     ║ AGE ║ ID ║ QTY ║
╠══════════════╬═════╬════╬═════╣
║ firstname 1  ║  20 ║  1 ║   2 ║
║ firstname 2  ║  21 ║  2 ║   3 ║
║ firstname 3  ║  22 ║  3 ║   4 ║
║ firstname 4  ║  23 ║  4 ║   5 ║
║ firstname 5  ║  24 ║  5 ║   6 ║
║ firstname 6  ║  25 ║  6 ║   7 ║
║ firstname 7  ║  26 ║  7 ║   8 ║
║ firstname 8  ║  27 ║  8 ║   9 ║
║ firstname 9  ║  28 ║  9 ║  10 ║
║ firstname 10 ║  29 ║ 10 ║  11 ║
╚══════════════╩═════╩════╩═════╝

I wish to finish this within 1 linq statement. (if totally impossible, 2 statements)

I have worked my statement this far.

 var resultQuery = from a in dtbl1.AsEnumerable()
                      join b in dtbl2.AsEnumerable()
                      on a.Field<int>("ID") equals b.Field<int>("LinkID")

                      select new
                      {
                          ID = a["ID"],
                          Name = a["Name"],
                          Age = a["Age"],
                          Qty = b["Quantity"]
                      }
                      ;

That statement will combine 2 tables, but I dunno how to continue to count the for the quantity.

Any idea?


If I must use a second query, It has been done.

 var secondResultQuery = from row in resultQuery
                            group row by new { row.ID, row.Name, row.Age } into g
                            select new
                            {
                                g.Key.ID,
                                g.Key.Name,
                                g.Key.Age,
                                totalCount = g.Sum(count => (int)count.Qty)
                            };

 

 

————————————————-

Answer

var resultSet   =   from a in dtbl1.AsEnumerable()
                    join b in dtbl2.AsEnumerable()
                        on a.ID equals b.LinkID
                    group a by new
                        {
                            a.Name,
                            a.Age,
                            a.ID
                        } into g
                    select new
                        {
                            g.Key.Name,
                            g.Key.Age,
                            g.Key.ID,
                            g.Count()
                        };

Query to work with DataTable

var resultSetTicketNo   =   from a in dtbl1.AsEnumerable()
                        join b in dtbl2.AsEnumerable()
                        on a.Field<int>("ID") equals b.Field<int>("LinkID")
                        group a by new
                        {
                            Age = a["Age"],
                            ID = a["ID"],
                            Name = a["Name"]
                        } into g
                        select new
                        {
                            g.Key.Name,
                            g.Key.Age,
                            g.Key.ID,
                            Counter=g.Count()
                        };

    foreach (var a in resultSetTicketNo)
    {
        Response.Write(a.ID + "~" + a.Name + "~" + a.Age + "~" + a.Counter + "<br/>");
    }

    Response.Write("<br/><br/>end of resultSetTicketNo<br/><br/>");

.net,c#,linq

[], [], []

Facebook Comments

Post a comment