group by in lambda expression mvc with c#

Question

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..

Answer 1

You'll want a completely different query and viewmodel to generate the second sum of the items because they are generated from a different perspective as far as the data is concerned.

You could do this:

        var groupedValues = viewModels.ToList()
            .GroupBy(model => model.CostCode,
                (i, models) => new {CostCode = i, Sum = models.Sum(model => model.Value)});

That will create an anonymous type that has a cost code and sum field you can access and display in your view.

Alternately, you can also declare a new viewmodel and populate that:

public class CostCodeSummaryViewModel
{
    public int CostCode { get; set; }
    public int Total { get; set; }
}


        var groupedValues = viewModels.ToList()
            .GroupBy(model => model.CostCode,
                (i, models) => new CostCodeSummaryViewModel {CostCode = i, Total = models.Sum(model => model.Value)});

Answer 2

You should use model which holds total and details for each category of given cost code. Then you should group transactions by cost code and select these models. Inside each cost code group you should create another grouping by category, and select models which contains summary of given cost code/category group. Here is sample with anonymous types:

var query = from t in db.CURRENT_JCT_TRANSACTION
            group t by t.Cost_Code into g
            orderby g.Key ascending
            select new
            {
                CostCode = g.Key,
                Total = g.Sum(t => t.Amount),
                Categories = 
                    from t in g
                    group t by t.Category into cg
                    select new
                    {
                        CostCode = g.Key,
                        Category = cg.Key,
                        Amount = cg.Sum(t => t.Amount)
                    }
            };

Output:

[
  {
    "CostCode": 1001,
    "Total": 420.0,
    "Categories": [ 
      { "CostCode": 1001, "Category": "E", "Amount": 100.0 },
      { "CostCode": 1001, "Category": "L", "Amount": 200.0 },
      { "CostCode": 1001, "Category": "OH", "Amount": 120.0 }
    ]
  },
  {
    "CostCode": 1002,
    "Total": 400.0,
    "Categories": [
      { "CostCode": 1002, "Category": "L", "Amount": 100.0 },
      { "CostCode": 1002, "Category": "LB", "Amount": 100.0 },
      { "CostCode": 1002, "Category": "OH", "Amount": 200.0 }
    ]
  }
]