none
Need a Query for two joined tables RRS feed

  • Question

  • i have 2 tables:

    ---1

    tbDetail:

    id     idHeader    name

    1          1           A

    2          1           B

    3          1           C

    4          2           A

    5          2           F

    ---2

    tbHeader:

    id   year     Month

    1    2015      1

    2    2015      3

    2    2014      2

    -----

    How do I output by linq this?

    tbHeader.id          tbHeader.year            tbHeader.month              Count_of_detail

    1                          2015                            1                                3

    2                          2015                            3                                2

    3                          2014                            2                                0


    • Edited by Ashkan209 Sunday, October 25, 2015 7:52 AM
    Sunday, October 25, 2015 7:51 AM

Answers

  • Hi,

    Based on your table, the equivalent query for this is the following,

    Select h.id As tbHeaderId, h.year As tbHeaderYear, h.Month As tbHeaderMonth, Count(d.idHeader) As Count_of_details 
    	From tbDetail d Right Outer Join tbHeader h On d.idHeader = h.id
    	Group By h.id, h.year, h.Month

    Then if we try to convert this query to Linq, this would be the result,

    using (DataClasses1DataContext db = new DataClasses1DataContext())
                {
                    var results = from headers in db.tbHeaders
                                  join details in db.tbDetails on headers.id equals details.idHeader into t1
                                  from t2 in t1.DefaultIfEmpty()
                                  group t2 by new { headers.id, headers.Month, headers.year } into gr
                                  select new
                                  {
                                      tbheaderId = gr.Key.id,
                                      tbHeaderYear = gr.Key.year,
                                      tbHeaderMonth = gr.Key.Month,
                                      Count_of_details = gr.Count(x => x.idHeader != null)
                                  };
                    dataGridView1.DataSource = results;
                }

    Monday, October 26, 2015 3:44 AM