order by LINQ (vb.net)

[lulu79]

I have a LINQ function to get 3 things :: Line, Average of Duration and Count
I have a problem to sort it based on Average Of Duration column.

Here is my code:

Dim group2 As IEnumerable(Of DataRow) = _
                From row In Ds.Tables(0).AsEnumerable() _
                    Group row By LINENAME = row.Field(Of String)("LINENAME") Into grp = Group _
            Order By (LINENAME >= Convert.ToString(LINENAME)) _
            Select DTRow(grpTot, _
               New Object() {LINENAME, _
                             New TimeSpan(CType( _
                                grp.Average(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks),  _
                                Int64 _
                                ) _
                                ), _
                                grp.Count() _
                                } _
                                )

How can I sort my LINQ based on the Average, not based on LINENAME like the above code?

[john.knapp]

The GROUP BY clause allows you to associate the Average values with a specific item, if you just want them to display in a different order, look into ORDER BY

[TnTinMN]

Hi Lulu,

You are sure beating that example code to death! :-)

You should really post more of the code when you are asking for help. People are not going to know what DTRow is for example.

Oh well, so much fo my pedantic streak. Here is one way you could do it.

      Dim group As IEnumerable(Of DataRow) = _
         From sortrow As DataRow In ( _
         From row In DS.Tables(0).AsEnumerable() _
         Group row By LINENAME = row.Field(Of String)("LINENAME") Into grp = Group _
         Order By LINENAME _
         Select DTRow(grpDT, _
                      New Object() {LINENAME, _
                                    myToday.Add( _
                                                New TimeSpan(CType( _
                                                                     grp.Average(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks),  _
                                                                     Int64 _
                                                                   ) _
                                                            ) _
                                               ) _
                                   } _
                     ) _
         ) Order By sortrow.Item("EVENTTM")

Or you could just do it on your datatable.

      'Add the query results to the new Table
      For Each row As DataRow In group
         grpDT.Rows.Add(row)
      Next

      grpDT.DefaultView.Sort = "[EVENTTM] Asc" ' for ascending


      'grpDT.DefaultView.Sort = "[EVENTTM] Desc" ' for descending 


      'Assign new Table as DataSource of DGV
      GridView1.DataSource = grpDT.DefaultView

Select one way or the other. Not Both.

[lulu79]

thank you TnTinMn.. i tried both suggestion and only the first one give me the desired result.

TnTinMn,
I have questions to ask you.

First, My application that use LINQ has to auto refresh every 30 minutes. I'm using this code in client side code.

<meta http-equiv="refresh" content="1800">

But the problem is, sometimes it works fine but sometimes it give me run-time error.
I'm using the same refresh code to the other page that not use LINQ and it works without giving any error.
Is this because the LINQ make my application slow until it throws the runtime error?

Second, I want to draw a horizontal line in my chart. Before this i use below code to draw it but when i try to draw with this application (y axis in timespan), it give me error like 'make sure not dividing by zero'

  Private Sub Chart1_PostPaint(ByVal sender As Object, ByVal e As ChartPaintEventArgs) Handles Chart1.PostPaint

        If TypeOf e.ChartElement Is ChartArea Then

            Dim area As ChartArea = CType(e.ChartElement, ChartArea)

            Dim x1 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Minimum))
            Dim x2 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Maximum))
            Dim y As Single = CSng(area.AxisY.ValueToPixelPosition(0.3))

            e.ChartGraphics.Graphics.DrawLine(New Pen(Color.Blue, width:=5), x1, y, x2, y)

        End If

Actually, I dont know what to put in the axisy.valuetopixelposition() because when i try to put something like 00:03:00 it give me syntax error.

[lulu79]

Forgot to tell you, this application is the continuation from here:http://www.daniweb.com/software-development/vbnet/threads/434993/chart-from-dataset

[TnTinMN]

What is the error message?
Linq is not the fastest in the world. How many data points do you have (approximately)?

For drawing the line, you need to scale the position to the axis as the labels are not the axis values. We had to fake it into using the timespan values with the custom labels.

     Private Sub Chart1_PostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataVisualization.Charting.ChartPaintEventArgs) Handles Chart1.PostPaint
          If TypeOf e.ChartElement Is ChartArea Then

              Dim area As ChartArea = CType(e.ChartElement, ChartArea)

              Dim x1 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Minimum))
              Dim x2 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Maximum))

              ' need to scale the targetY value to the y-Axis values

              Dim targetTS As New TimeSpan(0, 3, 0) 'your 00:03:00

              ' when we originally setup the Y-Axis we used OADates to create a numeric value
              ' now convert these back to .Net datetime and get the range in Ticks
              Dim deltaTSticks As Int64 = DateTime.FromOADate(area.AxisY.Maximum).Ticks - DateTime.FromOADate(area.AxisY.Minimum).Ticks

              ' It is a linear scale:  y = y(0) + slope * X
              ' where X is targetTS.tick
              '       slope = (area.AxisY.Maximum - area.AxisY.Minimum) / deltaTsticks
              '       y(0) = area.AxisY.Minimum 
              '       y = targetY
              Dim targetY As Single = CSng(area.AxisY.Minimum + ((targetTS.Ticks / deltaTSticks) * (area.AxisY.Maximum - area.AxisY.Minimum)))

              ' now convert to pixel position
              Dim y As Single = CSng(area.AxisY.ValueToPixelPosition(targetY))

              e.ChartGraphics.Graphics.DrawLine(New Pen(Color.Blue, Width:=5), x1, y, x2, y)

          End If
     End Sub

[lulu79]

TnTinMn, thank you for your support.

But I got line like below:

The line is at 00:05:00 something, not 00:03:00.
Is it because we use the linear calculation so that the line is not fixed to 00:03:00?
Is there has any other way I can draw a fixed line at 00:03:00?

Sorry TnTinMn if I always bothering you :(

[TnTinMN]

I'm getting old and sloppy in my coding; it is a datatype precision mistake.

change
Dim targetY As Single = CSng(area.AxisY.Minimum + ((targetTS.Ticks / deltaTSticks) * (area.AxisY.Maximum - area.AxisY.Minimum)))

to

Dim targetY As Double = (area.AxisY.Minimum + ((targetTS.Ticks / deltaTSticks) * (area.AxisY.Maximum - area.AxisY.Minimum)))

[lulu79]

Thank you TnTinMN. Now it's working fine.

One more question, is the Chart control only accept Single datatype to do any event like above? (Example to Draw linear line)

Because currently I have to change the bar chart color, from blue to red, if the bar exceed the limit (00:03:00)

Before this I use below code to do it with other application that consist of single datatype data and it's working fine.
But when I want to do it with this application, I got stuck on how to convert 00:03:00 to single datatype.
Here is the code:

  For Each dp As DataPoint In Chart1.Series("Wilma").Points
            If dp.YValues(0) > 25 Then
                dp.Color = Color.Crimson
            End If
        Next

I try change 25 to "00:03:00" but of course it throw error like below (Conversion from 'string' to 'double' is not valid):

How can I fix this?

[TnTinMN]

The DataPoints primarily Type Double or something that can be converted to double. Think it logically, it needs plots floating pont values.

In this application, the Y-Axis values are AODate values that in turn are Type Double. The YAxis.Minimum value is your relative zero point. To get a TimeSpan value 3 minutes greater than that:

if dp.YValues(0) > DataTime.FromOADate(Chart1.ChartArea(0).AxisY.Minimum).Add(New TimeSpan(0,3,0)).ToAODate then

[lulu79]

TnTinMn, is it 'DataTime' or 'DateTime'? Because when i wrote datatime it has error:
Name 'DataTime' is not declared

Then I changed to 'DateTime' and got another error:
ToAODate' is not a member of 'Date'

Then I changed ToAODate to FromOADate and get this error:
Argument not specified for parameter 'd' of 'Public Shared Function FromOADate(d As Double) As Date'.

How to fix it?

Anyway this is the code:

   Private Sub Chart1_Customize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Chart1.Customize


        For Each dp As DataPoint In Chart1.Series("AVERAGE LOST TIME").Points

            If dp.YValues(0) > DateTime.FromOADate(Chart1.ChartAreas("Fred").AxisY.Minimum).Add(New TimeSpan(0, 3, 0)).FromOADate Then

                dp.Color = Color.Crimson
            End If
        Next

    End Sub

[TnTinMN]

that's what I get from free handing it; complaints about 2 transposed leters that you then turn into a whole different method. :-/

If dp.YValues(0) > DateTime.FromOADate(Chart1.ChartAreas("Fred").AxisY.Minimum).Add(New TimeSpan(0, 3, 0)).ToOADate Then

[lulu79]

TnTinMn,
it works!
thanks again!

And again, i learned many new things in programming world,
thanks a lot to you!

May God bless you :)