LINQ Aggregation from a Datatable to a Chart Control RRS feed

  • Question

  • I haven't been able to find these answers, although I am pretty sure this stuff can be done. A little background on me ..... I am a mainframer by trade, but am putting up a reporting site for out team (please be patient ..... I am not a distributed guy ... yet!). I have successfully crunched my raw source data into the format I need (see dtOutages below ... contains data for all possible user selections), but now I need to aggregate the detail-level data from my table and then bind it to a Chart Control. Can the Chart Control use LINQ as a datasource? The following is the actual DataTable (dtOutages) which can only contain single-day records for a single system (no record will span multiple days ... those have already been broken out ..... more than 1 row per outage indicates multiple-day outage .... and no, they are not complete outages :) ). While I can feed anything in the DataTable to the Chart Control, I need to roll things up rather than having all of thse individual points.


    OutageNbr SystemName OutageStartDt OutageStartTmstmp OutageEndTmstmp TotNumUsers OutageLength Year YearMonth Month YearWeek MonthWeek
    1 SystemA 10/07/2008 10/07/2008 13:57 10/07/2008 23:59 44 602 2008 2008.10 10 2008.40 OCT08 WK1
    1 SystemA 10/08/2008 10/08/2008 0:00 10/08/2008 19:14 84 1154 2008 2008.10 10 2008.40 OCT08 WK1
    2 SystemA 10/15/2008 10/15/2008 14:14 10/15/2008 15:40 33 86 2008 2008.10 10 2008.41 OCT08 WK2
    3 SystemA 10/17/2008 10/17/2008 7:47 10/17/2008 9:15 8 88 2008 2008.10 10 2008.41 OCT08 WK2
    5 SystemA 10/29/2008 10/29/2008 6:54 10/29/2008 23:59 200 1025 2008 2008.10 10 2008.43 OCT08 WK4
    5 SystemA 10/30/2008 10/30/2008 0:00 10/30/2008 10:22 121 622 2008 2008.10 10 2008.43 OCT08 WK4
    6 SystemA 10/31/2008 10/31/2008 7:51 10/31/2008 9:30 9 99 2008 2008.10 10 2008.43 OCT08 WK4
    7 SystemA 10/31/2008 10/31/2008 10:27 10/31/2008 23:54 65 807 2008 2008.10 10 2008.43 OCT08 WK4
    8 SystemA 11/03/2008 11/03/2008 12:50 11/03/2008 14:35 27 105 2008 2008.11 11 2008.44 NOV08 WK1
    9 SystemA 11/04/2008 11/04/2008 11:36 11/04/2008 12:17 4 41 2008 2008.11 11 2008.44 NOV08 WK1
    10 SystemA 11/04/2008 11/04/2008 13:25 11/04/2008 14:43 52 78 2008 2008.11 11 2008.44 NOV08 WK1
    11 SystemA 11/05/2008 11/05/2008 12:37 11/05/2008 13:15 2 38 2008 2008.11 11 2008.44 NOV08 WK1
    12 SystemA 11/09/2008 11/09/2008 9:14 11/09/2008 10:26 2 72 2008 2008.11 11 2008.44 NOV08 WK1

    What I need to do based on various criteria will be to do something like this type of SQL:

    Avg Outage Time  and Tot # Users Affected by MonthWeek:
    SELECT MonthWeek
              , AVG(OutageEndTmstmp-OutageStartTmstmp)/60 AS 'OUTAGE LEN (HRS)'
              , SUM(TotNumUsers) AS 'TOT # OF USERS'
    FROM dtOutages
    GROUP BY MonthWeek
    ORDER BY YearWeek //I doubt this is permissible as it is not in the SELECT, but need to sort from a sortable-type date field

    Once I get the result, I need to feed it to Chart1 (Outage Length will be Series1 and Tot#Users will be Series2). Can this be done from the LINQ result set (var)? How?
    Sorry I am not asking the questions in '.NET-ese' (give me a year of this), but I think (hope) you get my point. Consequently, please be descriptive with your answer if you can (if not, I have made everything else work up to this point pretty well, so I will figure it out). I haven't made a great connection to LINQ yet (I am very comfortable with SQL), and I need to get over that hump.

    Thanks for your help!

    • Edited by rickd59 Monday, April 6, 2009 7:18 PM typo
    Monday, April 6, 2009 7:02 PM

All replies

  • Hey Rick,

    One quick question first, what is the specific type of Chart control you are using? What is the full type name of it? For instance DataGridView would be System.Windows.Forms.DataGridView or System.Data.DataSet.

    What you could do is not use the linq expressions at all. You could use Calculated expressions. You could create two more columns called Avg and Sum and in each column there is an expression property.
    The link here points to documentation on how to write a calculated expression.

    Now is the output that you are describing on the chart control, will the user need to be able to edit some of the data? The only reason I ask is that if you write a linq expression that is a projection (IE selecting particular columns that you want) the result will not be DataRows so this will not be updatable. when you update a property it will not be updated in the DataTable.

    You can write a linq expression. Below is a link to writing the expressions in C#

    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, April 6, 2009 8:41 PM
  • Hi Chris,

      Thanks for your response. I am sorry, I did not realize that 'Chart Control' was ambiguous. I am using the actual control called Chart Control (it is a plugin for 3.5 from MS for graphing ... it is like Dundas Chart), and it can be found in System.Web.UI.DataVisualization.Charting (it has to be downloaded though as it is not native with 3.5 or SP1). If you have any visual reporting needs, it is a great control. A couple of points from your comments:

         *There is no need to update any rows in dtOutages as it is read-only at this point. Items simply need to be grouped and displayed.
         *Are datacolumn expressions capable of rolling up multiple groups or do you get a single row/value for each column? As an example, 
           if I group by day, I want the min, max, tot of each day and each day may have 0 to many outages to roll up. It seems to me that
           if I use datacolumn expressions I will only get a single output row where I need several grouped on one or several columns of
           dtOutages. I then need to display the values for each rollup as a point in my chart (if these results get written back to the
           datatable I guess I could put a rowType (set to '99' or something) and then filter these back out. Can you give me a little more
           info as to if this is possible .... it is not apparent from the link you sent. I will keep digging on that as well. Thanks!

    Again, my two basic problems are that
         1.) I am not sure how to rollup (group) the detailed data that I have in my datatable (but I think LINQ is probably the ideal fit here ..... but I am willing to defer to the experts), and then,
         2.) with whatever option is selected, I need to use the resulting view of that data as the datasource for my Chart Control.


    • Edited by rickd59 Tuesday, April 7, 2009 1:04 PM typos
    Tuesday, April 7, 2009 1:00 PM