Databases
StackOverflow2013
Postsdbo
POSum and grouping using linq
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSum and grouping using linq
    primarykey
    data
    text
    <p>I have been trying to compile data from a table that looks like image1 and convert and display it to a table like image2.</p> <p>Image1</p> <p><a href="http://i44.tinypic.com/fjesl2.png" rel="nofollow noreferrer">img1 http://i44.tinypic.com/fjesl2.png</a></p> <p>Image2</p> <p><a href="http://i39.tinypic.com/el790o.png" rel="nofollow noreferrer">img1 http://i39.tinypic.com/el790o.png</a></p> <p>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.</p> <p>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</p> <p>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.</p> <p>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.</p> <p>Here are my classes:</p> <pre><code>public class Region { public List&lt;Server&gt; Servers { get; set; } } public class Server { public string ServerID { get; set; } public string ServerName { get; set; } public List&lt;CostData&gt; MonthlyCosts { get; set; } } public class CostData { public string Date { get; set; } public double Sum { get; set; } } </code></pre> <p>This is how I am building my objects:</p> <pre><code>Region r = new Region(); r.servers = new List&lt;Server&gt;(); foreach (var row in linqQueryResult) { Server s = new Server(); s.ServerID = row.ServerID; s.ServerName = row.ServerName; s.MonthlyCosts = new List&lt;CostData&gt;(); 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); </code></pre> <p>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 :)</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USAshkan Hovold
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSum and grouping using linq
      1. This table or related slice is empty.
      1. VTDownMod
    1. COIt'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...
      singulars
      1. POSum and grouping using linq
      1. USIt'sNotALie.
    2. COMaking 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...
      singulars
      1. POSum and grouping using linq
      1. USAshkan Hovold
    3. CODo you mind if I restructure your classes/instantiation statement a bit?
      singulars
      1. POSum and grouping using linq
      1. USIt'sNotALie.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload