Aggregate

cilerler
cilerler used Ask the Experts™
on
I would like to return query below with the additional column which multiplies one of the column called "CalculatedRate" with previous record of the same column.  (Initial previous value is 1)

Dim DividendList4Final = From dividendItem In DividendList4Update
Select dividendItem

Open in new window


Here is the part I could do with For but I would like to do this in Linq and want to see it in same result
                Dim rate As New List(Of Double)
                Dim currentRate As Double = 1
                For Each dividendItem In DividendList4Final
                    currentRate *= dividendItem.dividendCalculatedRate
                    rate.Add(currentRate)
                Next

Open in new window


Thank you for your time and attention
aggregate.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi cilerler;

This type of code is not what you use Linq for. The first line of the For Each requires access to the previous value which is not available in the middle of a Linq query. Think of Linq query as a conveyor belt and each operator is a break in that conveyor which start the next section of the conveyor belt. You only have access to the list at the breaks and not what happen between the beginning and end of each section.

                Dim rate As New List(Of Double)
                Dim currentRate As Double = 1
                For Each dividendItem In DividendList4Final
                    currentRate *= dividendItem.dividendCalculatedRate
                    rate.Add(currentRate)
                Next

Fernando

Author

Commented:
@Fernando => Thank you for reply.

Here is what we can do with Linq based on the sample I gave above
As you can see It is perfectly possible to add additional column even call a function.

But what I need is Aggregate version of this function and instead of Sum it should be Multiply.
Again all i need is be able to put previous row's value instead of 3 in this sample. :)

thx again...


Dim DividendList4Final = From dividendItem In DividendList4Update
Let currentRate = dividend.CalculatedRate * 3
Select dividendItem, currentRate

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
You stated in one of your other questions, "latest XYZ is at 12/10/2010 and rate is 3.81 which sets CalculatedRate as same with Rate which is 3.81 next XYZ is at 9/10/2010 and rate is 1.11 which means we need to multiply it with previous one 1.11*3.81=4.2291 next XYZ is at 6/10/2010 and rate is 2.49 multiply it with previous one 2.49*4.2291=10.530459", Do you not want to do the calculations as stated in your statement?
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
Let's not mix two Questions.  In this question all i want todo is multiply row with previous row regardless of the conditions etc.

Author

Commented:
Experts,

I can do this like the code I attached in order to get result in single pass.  But it is not the case here, i need AGGREGATED solution.  I have over 10Millions row and really don't want to make another loop for just adding one column.  Of course this is the simplified version of my problem so please stay away from the shortcut solutions.

Thank you very much for your time and attention.
Public Shared _myVariable As Double

Public Shared Function Multiply(ByVal myvalue As Double) As Double
       _myVariable *= myvalue
       Return _myVariable
End Function

Sub Main
       Dim DividendList4Final = From dividendItem In DividendList4Update
       Let myRate = Multiply(dividendItem.CalculatedRate)
       Select dividendItem, myRate
End Sub

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi cilerler;

Although your code from your last post will work it will also add a big amount of time to do it. For every call to Multiply(...) will cause a context switch to occur on each call which will add a large amount of time to complete the query. Although you can access a value outside of the scope of the query you can not update a variable outside of the scope of the query directly.

In the code snippet below should work as yours except should not suffer from context switching. Also the select clause uses the dividendItem object you can break out all of its fields so that the result set is flat.

Fernando
Public Shared _myVariable As Double

Sub Main
    Dim DividendList4Final = From dividendItem In DividendList4Update _
                             Select New With { _
                                 .dividendItem = dividendItem, _
                                 .Rate = dividendItem.CalculatedRate.Multiply(_myVariable) _
                             }
End Sub


' This is an extension method much like the ones Linq creates
' Must be defined in a module
Module Extensions

    <System.Runtime.CompilerServices.Extension()> _
    Public Function Multiply(ByVal myvalue As Double, ByRef myVariable As Double) As Double
        myVariable *= myvalue
        Return myVariable
    End Function

End Module

Open in new window

Author

Commented:
Thx Fernando, I'm aware of that...  (just tried to give as simple as possible)
What about aggregate option?  Any idea about it?  Thanks again.
Retired
Distinguished Expert 2017
Commented:
Hi cilerler:

From Microsoft documentation: http://msdn.microsoft.com/en-us/library/bb548651.aspx
The Aggregate method makes it simple to perform a calculation over a sequence of values. This method works by calling func one time for each element in source. Each time func is called, Aggregate passes both the element from the sequence and an aggregated value (as the first argument to func). The first element of source is used as the initial aggregate value. The result of func replaces the previous aggregated value. Aggregate returns the final result of func.

The Aggregate method works on a sequence from beginning to end and returns a single value. This is the same for Count, Sum, Min, Max, and Average methods so will not serve your needs.

Fernando

Author

Commented:
thanks Fernando, hope someone else will have different approach than we did...
Fernando SotoRetired
Distinguished Expert 2017

Commented:
So what is the problem using the Extension method?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial