group by with lambda expression in mvc c#

Question

Hello Friends,

 

I have a lambda expression which returning correct values but i want sum of grouped items as well, here my query

 

IEnumerable<CJTViewModel> objModel = (from q in db.CURRENT_JCT_TRANSACTION

where q.Job == job

group q by new { q.Cost_Code, q.Category } into g

select new CJTViewModel()

{

Job = job,

Category_E = g.Key.Category == "E" ? g.Sum(s => s.Amount) : 0,

Category_L = g.Key.Category == "L" ? g.Sum(s => s.Amount) : 0,

Category_LB = g.Key.Category == "LB" ? g.Sum(s => s.Amount) : 0,

Category_OH = g.Key.Category == "OH" ? g.Sum(s => s.Amount) : 0,

Cost_Code = g.Key.Cost_Code,

Category = g.Key.Category

}).ToList().OrderBy(x => x.Cost_Code );

 

It gives  me output like below

 

Cost Code    Category      Amount

------------------------------------------------ 

1001               E                    $100

1001               L                    $200

1001               OH                $120

1002               L                    $100

1002               LB                  $100

1002               OH                 $200

 

but i wants output like below 

 

Cost Code      Category     Amount

----------------------------------------------

1001                    E                  $100

1001                    L                  $200 

1001                    OH              $120

                                          -----------------

                                                 $420   

1002                    L                  $100

1002                    LB                $100

1002                    OH               $200

                                          -----------------

                                                 $400 

 

Any help would be appreciated..