Sum and grouping using linq

Question

I have been trying to compile data from a table that looks like image1 and convert and display it to a table like image2.

Image1

Image2

First I figured I would do like this: One class called Region that contains a list of the class called server The server class would have 3 properties serverid, servername and a list of the class called CostData. CostData would store year-month and sum of the cost for the month.

I have managed with linq to get a query that gives me the total for each server per month, like: Year 2013, Month: 1, Server: Server1, Total: 460

Still I find myself short of a few things. First I need to put each servers monthly totals in one place, like what I've tried with my CostData, so that I with html can just iterate each servers month and display them in columns.

Also, with this solution I have still missed the CostDesc column which I also want to display the monthly total for like you can se on image2.

Here are my classes:

public class Region
{        
public List<Server> Servers { get; set; }
}

public class Server
{               
public string ServerID { get; set; }
public string ServerName { get; set; }
public List<CostData> MonthlyCosts { get; set; }
}

public class CostData
{
public string Date { get; set; }
public double Sum { get; set; }
}

This is how I am building my objects:

Region r = new Region();
r.servers = new List<Server>();

foreach (var row in linqQueryResult)
    {
            Server s = new Server();
            s.ServerID = row.ServerID;
            s.ServerName = row.ServerName;
            s.MonthlyCosts = new List<CostData>();
            CostData cd = new CostData();
            cd.Date = row.Year.ToString() + "-" + row.Month.ToString();
            cd.Sum = row.ServerSum;
            s.MonthlyCosts.Add(cd);                    
            r.Servers.Add(s);

    }


    return View(r);

Do you have any pointers or suggestions? I'm hoping somebody with more experience could take a look and give me some advice, not asking for the entire solution although if you wish to do it I would not mind :)

I wonder if there isn't a good way to get the data I want using linq. The catch to this is that I only have access to a stored procedure that will only give me data like in image1. All the manipulation with the data must be done locally with c#, although that probably isn't such a bad thing.

It'd be a bit easier for us if you could use a `DateTime` instead of a `string` for the date, or at least separate `int` fields for Month and Year. Are you able to do that? Also, it seems like you're only adding one `CostData` per server... — It'sNotALie., Jun 03 '13 at 20:23
Making the date a DateTime is no problem. Getting all the month stat for each server into one place is one of the things I'm having problem with. I should perhaps use a hashtable or dictionary to build them together somehow, cant figure out how exactly though... — Ashkan Hovold, Jun 03 '13 at 20:31
Do you mind if I restructure your classes/instantiation statement a bit? — It'sNotALie., Jun 03 '13 at 20:32
Not at all, go ahead. I figure that there is a flaw in that setup anyway, because I need to include the CostDesc monthly total as well, I have been stuck on getting the server monthly totals to work, I figure if I can crack that I can do the same with CostDesc. — Ashkan Hovold, Jun 03 '13 at 20:34
Actually I just added that in the excel file myself. I use a sum in my linq query later on but the table only gives me ammount and price. — Ashkan Hovold, Jun 03 '13 at 20:51
So... what should I put for `double Sum` in CostData? (Note: may not finish this until tomorrow) — It'sNotALie., Jun 03 '13 at 20:53
I thouht you were talking about Total on image1. The Sum property on CostData is the total cost for one server/month. No problem, I'm gratefull for anyhelp — Ashkan Hovold, Jun 03 '13 at 20:55

score 2 · Accepted Answer · edited May 23 '17 at 12:01

You need to do what is commonly known as a PIVOT, or a crosstab. This should help with that: Is it possible to Pivot data using LINQ?

Then it appears you want to do a totaling function that adds special total rows, and then possibly a custom sort order. After you get the pivot done, the rest is fairly easy though.

The total rows can be achieved by doing a separate query after you've done your pivot to total the rows up (group by). Then union the two results together. If you add a sort column, then you can sort the results and finally get your finished resultset.

Sum and grouping using linq

1 Answers1