StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLINQ to SQL and a running total on ordered results
text
Body
copied!<p>I want to display a customer's accounting history in a <code>DataGridView</code> and I want to have a column that displays the running total for their balance. The old way I did this was by getting the data, looping through the data, and adding rows to the <code>DataGridView</code> one-by-one and calculating the running total at that time. Lame. I would much rather use LINQ to SQL, or LINQ if not possible with LINQ to SQL, to figure out the running totals so I can just set <code>DataGridView.DataSource</code> to my data.</p> <p>This is a super-simplified example of what I'm shooting for. Say I have the following class.</p> <pre><code>class Item { public DateTime Date { get; set; } public decimal Amount { get; set; } public decimal RunningTotal { get; set; } } </code></pre> <p>I would like a L2S, or LINQ, statement that could generate results that look like this:</p> <pre><code> Date Amount RunningTotal 12-01-2009 5 5 12-02-2009 -5 0 12-02-2009 10 10 12-03-2009 5 15 12-04-2009 -15 0 </code></pre> <p>Notice that there can be multiple items with the same date (12-02-2009). The results should be sorted by date before the running totals are calculated. I'm guessing this means I'll need two statements, one to get the data and sort it and a second to perform the running total calculation.</p> <p>I was hoping <code>Aggregate</code> would do the trick, but it doesn't work like I was hoping. Or maybe I just couldn't figure it out.</p> <p>This <a href="https://stackoverflow.com/questions/1576816/linq-running-total-and-sub-total">question</a> seemed to be going after the same thing I wanted, but I don't see how the accepted/only answer solves my problem.</p> <p>Any ideas on how to pull this off?</p> <p><strong>Edit</strong> Combing the answers from Alex and DOK, this is what I ended up with:</p> <pre><code>decimal runningTotal = 0; var results = FetchDataFromDatabase() .OrderBy(item => item.Date) .Select(item => new Item { Amount = item.Amount, Date = item.Date, RunningTotal = runningTotal += item.Amount }); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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