none
linq to sql - join and group RRS feed

  • Question

  • Need help with my Linq to SQL statement which joins the account master to order file. Group by is used to SUM the order amounts for the customer.

    Here is the SQL I want to implement:
    select  a.sn, a.mbrnu, sum(coalesce(b.bal,0)) 
    from      acctmstrp a                         
    join      qtemp/arbal0602 b                   
    on        a.sn = b.sn and a.mbrnu = b.mbrnu   
    group by  a.sn, a.mbrnu                       
    and what I have so far. The compiler is complaining that "b does not exist in the current context.

         var xx = from a in db.AccountMasterRows
                   join b in db.OrderBalanceRows
                   on new { a.SN, a.Mbrnu } equals new { b.SN, b.Mbrnu }
                   group a by new {a.SN, a.Mbrnu} into g
                   select new AccountSummary
                   {
                     SN = g.Key.SN,
                     Mbrnu = g.Key.Mbrnu,
                     AmtDue = g.Sum(b.BalAmt)
                   };
    

    how do I code the join and group by in LINQ to SQL?

    thanks,

    Wednesday, June 3, 2009 5:45 PM

Answers


  • My bad... I wasn't looking at it careful enough, thought you just had a typo.  Tricky... Try this one.


                var xx = from a in db.AccountMasterRows
                         join b in db.OrderBalanceRows
                           on new { a.SN, a.Mbrnu } equals new { b.SN, b.Mbrnu }
                         group new { b.BalAmt } by new { a.SN, a.Mbrnu } into g
                         select new
                         {
                             SN = g.Key.SN,
                             Mbrnu = g.Key.Mbrnu,
                             AmtDue = g.Sum(b => b.BalAmt)
                         };
    
    • Marked as answer by Steve Richter Wednesday, June 3, 2009 7:00 PM
    Wednesday, June 3, 2009 6:58 PM

All replies


  • I think you are looking good so far... Change the AmtDue to this:

         AmtDue = g.Sum(b => b.BalAmt)

    The parameter to Sum() needs to be a lambda function.
    Wednesday, June 3, 2009 6:21 PM
  • doesn't work.  When I group "a" into "g". I am losing the join to "b".

         var xx = from a in db.AccountMasterRows
                   join b in db.OrderBalanceRows
                   on new { a.SN, a.Mbrnu } equals new { b.SN, b.Mbrnu }
                   group a by new {a.SN, a.Mbrnu} into g
                   select new AccountSummary
                   {
                     SN = g.Key.SN,
                     Mbrnu = g.Key.Mbrnu,
                     AmtDue = g.Sum(b => b.BalAmt)
                   };
    

    Wednesday, June 3, 2009 6:33 PM

  • My bad... I wasn't looking at it careful enough, thought you just had a typo.  Tricky... Try this one.


                var xx = from a in db.AccountMasterRows
                         join b in db.OrderBalanceRows
                           on new { a.SN, a.Mbrnu } equals new { b.SN, b.Mbrnu }
                         group new { b.BalAmt } by new { a.SN, a.Mbrnu } into g
                         select new
                         {
                             SN = g.Key.SN,
                             Mbrnu = g.Key.Mbrnu,
                             AmtDue = g.Sum(b => b.BalAmt)
                         };
    
    • Marked as answer by Steve Richter Wednesday, June 3, 2009 7:00 PM
    Wednesday, June 3, 2009 6:58 PM
  • thanks!
    Wednesday, June 3, 2009 7:01 PM