c# - LINQ to SQL - Join, Group, and Sum -

i have following sql works expected:

select  p.acct_id [acct],      a.acct_name [acctname],      p.pd_no [period],      p.fy_cd [fy],      sum(cur_amt) [cactual],      sum(cur_bud_amt) [cbudget],      sum(ytd_amt) [yactual],      sum(ytd_bud_amt) [ybudget]  budgettotals p  inner join budgetaccounts      on p.acct_id = a.acct_id p.acct_id in ('610','620','630','634','641','642','643','644','646','665','620','dfc','dfr','dgn','dtx')      , fy_cd == "2013"      , pd_no == 12      , pool_no == 23  group p.acct_id, a.acct_name, p.pd_no, p.fy_cd; 

i'm trying convert above linq sql following:

var query = p in db.pool_sie_supports             join c in db.accts on p.acct_id equals c.acct_id             arr.contains(p.acct_id) && p.pool_no == 23 && p.fy_cd == "2013" && p.pd_no == 12             group p p.acct_id s             select new             {                 account = s.key,                 accountname = acct in db.accts select new { acct.acct_name },                 ctdactual = string.format("{0:c}", s.sum(y => y.cur_amt)),                 ctdbudget = string.format("{0:c}", s.sum(y => y.cur_bud_amt)),                 ytdactual = string.format("{0:c}", s.sum(y => y.ytd_amt)),                 ytdbudget = string.format("{0:c}", s.sum(y => y.ytd_bud_amt))            }; 

how can query return column accountname?

this assumes there 1 acct_name per given acct_id, otherwise you'll multiple records when group by:

var query = p in db.pool_sie_supports             join c in db.accts on p.acct_id equals c.acct_id             arr.contains(p.acct_id) && p.pool_no == 23 && p.fy_cd == "2013" && p.pd_no == 12             group p new { p.acct_id, p.acct_name } s             select new             {                 account = s.key.acct_id,                 accountname = s.key.acct_name,                  ctdactual = string.format("{0:c}", s.sum(y => y.cur_amt)),                 ctdbudget = string.format("{0:c}", s.sum(y => y.cur_bud_amt)),                 ytdactual = string.format("{0:c}", s.sum(y => y.ytd_amt)),                 ytdbudget = string.format("{0:c}", s.sum(y => y.ytd_bud_amt))            };