Help with Linq query syntax of DataTable => results used as datasource for Pie Chart

Question

I have a simple DataTable with two columns of pre-validated data (Xvalue, Yvalue) I would like to query and display as a Pie Chart.  The Xvalue column contains string category names and the Yvalue column contains values (typeOf Double).

I would like to visualize this data in a pie chart using a Linq query to present the data to msChart.  The visualization I am trying to achieve is a pie chart with section sizes determined by Category count, Percentage of total, or Average value for each category.  Using the internal datamanipulator in msChart, the Pie chart should look something like this:

Although the datamanipulator works OK, I am trying use Linq expressions for more granular control, but the syntax does not compile.  Can anyone suggest what might be wrong with the following Linq expression syntax for grouping by category and average value within each category.  The data table is dt:

                Dim q1 = From p In dt
                   Group p By category = p.Item(xy_xDataSeries)
                   Into catAvg = Average(Function(p) p.Item(xy_yDataSeries) as decimal)
                   Select New With {category, catAvg}

The compiler complains about a lot of things, but the squigly lines are typically under the "as decimal".

Thanks.

---

-BGood

Answer 1

Hi BGood;

Try the query like this.

Dim q1 = From p In dt.AsEnumerable() 
         Group p By category = p.Field(Of String)("xy_xDataSeries") Into catAvg = Group 
         Select New With { 
             .Category = category, 
             .CatAvg = catAvg.Average(Function(a) a.Field(Of Double)("xy_yDataSeries")) 
         }

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Hi Fernando,

Very nice, thankyou. 

I did have a run-time exception with an invalid cast, but it was because I forgot the the Y-value was Decimal rather than Double.  When this was corrected, it worked fine.

Thanks again.

---

-BGood

Answer 3

  

Not a problem BGood, glad I was able to help.

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".