How to sum a single row with multiple columns in linq query

Question

 (from item in _dbEntities.Bills
             where item.Year == year
             select item).GroupBy(x => x.MonthID).Sum(x => x.Phone);

I want to sum all utility bills month wise like January there are 4 bills electricity, phone, water,gas.. these are in a single row in table how to get sum of these all and groupby monthID

score 1 · Answer 1 · answered Feb 21 '15 at 21:18

This one return you total sum of all bills for each month (two fileds MonthID, Total):

var result = items.Where(b => b.Year == year)
    .Select(b => new { b.MonthID, Total = b.Electricity + b.Gas + b.Phone + b.Water })
    .GroupBy(b => b.MonthID)
    .Select(g => new { MonthID = g.Key, Total = g.Sum(i => i.Total) });

And this one return sum of each bill for each month (five fields: MonthID, TotalElectricity, TotalPhone, TotalWater, TotalGas):

var result = items.Where(b => b.Year == year)
    .GroupBy(b => b.MonthID)
    .Select(
        g => new {
            MonthID = g.Key,
            TotalElectricity = g.Sum(b => b.Electricity),
            TotalPhone = g.Sum(b => b.Phone),
            TotalWater = g.Sum(b => b.Water),
            TotalGas = g.Sum(b => b.Gas)
        }
    );

it returns a query i want a list or dictionary type so i can view data — Mike, Feb 21 '15 at 21:49

ocuenca · Accepted Answer · 2015-02-21T22:04:04.660

0

You could try this. :

var result= _dbEntities.Bills.Where(b => b.Year == year)
                             .GroupBy(b => b.MonthId)
                             .Select(g=> new { MonthId=g.Key,
                                               Phone=g.Sum(b=>b.Phone),
                                               Water = g.Sum(b => b.Water),
                                               Gas = g.Sum(b => b.Gas),
                                               Electricity = g.Sum(b => b.Electricity)                                         
                                             }).ToList();

I save the result in a anonymous type with five properties, the first one to save the MonthId, and the rest to save the sum of all the services in that particular month.

edited Feb 21 '15 at 22:04

answered Feb 21 '15 at 21:18

ocuenca

38,548
11
89
102

it returns a query i want a list or dictionary type so i can view data – Mike Feb 21 '15 at 21:50
well, you can call the `ToList` method or `ToDictionary` method at the end of the query. – ocuenca Feb 21 '15 at 22:02
but there is one other issue while calling these extension methods i am converting those all fields to int using int.parse(b.Phone) etc because they are in string format so when i use tolist or dictionary it gives the convert.toint32() error – Mike Feb 21 '15 at 22:06
"Additional information: LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression." – Mike Feb 21 '15 at 22:08
I understand what is your problem. Linq to Entities doesn't support that operation. At the end, your query will be translated to sql, that's way that operation is not allowed. After the `GroupBy` try to call the `AsEnumerable` method and after that apply the `Select` – ocuenca Feb 21 '15 at 22:13
could you please show the query that you are trying to execute? – ocuenca Feb 21 '15 at 22:21
it works you are right I just applied asenumerable before int.parse and it works thanks buddy – Mike Feb 21 '15 at 22:26
You welcome,BTW if you want to work with money I suggest you use the `decimal` type instead `int` – ocuenca Feb 21 '15 at 22:27

How to sum a single row with multiple columns in linq query

2 Answers2