Entity Framework getting a running total from columns

Question

This has probably been asked before, but I am struggling to find an answer. I have the below code, trying to get a running total.

This is not an error that I have seen before and stumped as to what it actually means, or how to fix it, and trying to keep this in one query if possible. However, will be open to better ways to do this.

I have added a comment where the error is occurring and what the error is inside Visual Studio.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.ObjectModel;

    class VMAccountWindow : BaseViewModel
    {

        public VMAccount()
        {
            using (DBContext db = new DBContext())
            {
                decimal currentTotal = 0;

                var tl = db.Transaction
                    .OrderByDescending(
                        p => p.TransactionDate
                    )
                    .Select(p => new TransactionList
                    {
                        AccountId = p.AccountId,
                        TransactionDate = p.TransactionDate,
                        Deposit = p.TransCode == "Deposit" ? p.TransactionAmount.ToString() : "",
                        Withdrawal = p.TransCode == "Deposit" ? "" : p.TransactionAmount.ToString(),
                        Notes = p.Notes,
                        // Following gives an error about Expression tree may not contain an assignment operator
                        AccountBalance = currentTotal += p.TransCode == "Deposit" ? p.TransactionAmount : -p.TransactionAmount
                    })
                .Where(o => o.AccountId == 1)
                .ToList();
            }
        }
    }

    public class TransactionList
    {
        public int AccountId { get; set; }
        public string TransactionDate { get; set; }
        public string Deposit { get; set; }
        public string Withdrawal { get; set; }
        public string Notes { get; set; }
        public decimal AccountBalance { get; set; }
    }

Any help would be appreciated.

If you try it with selecting an anonymous type and selecting just currenttotal. Still an error? — Andy, Sep 13 '20 at 09:21
Yes, and as in the link [here](https://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results/1834940#1834940) no matter, what I do I always get the same error with EF6. — Andrew Scott, Sep 13 '20 at 18:08

Andrew Scott · Accepted Answer · 2020-09-13T20:10:24.953

The solution was to turn the results into a list first, then do the second select, and I had not seen this as the searches I had done only shown get data. Without showing how.

As this may trip other new developers up, I have pasted the working example below, this is a result of all the suggestions from those who have tried to help.

var tl = db.Transaction
    .Where(o => o.AccountId == 1)
    .OrderBy(p => p.TransactionDate)
    .ToList()
    .Select(i =>
            {
                currentTotal += p.TransCode == "Deposit" ? p.TransactionAmount : 0;
                return new TransactionList
                {
                    ToAccountId = i.ToAccountId,
                    TransactionDate = i.TransactionDate,
                    AccountBalance = currentTotal
                };
            }
    )
    .ToList();

Unfortunately, I do not know the reason why this has to be turned to a list first, then do the select, so if anyone can chime in as to the reason, it would be appreciated if someone who knows could explain that part.

I **STRONGLY** suggest performing the `Where` (which is in the question, but not this answer) before the `ToList`, otherwise you're going to be loading the entire table every time. — Flater, Sep 13 '20 at 20:00
You are correct, I had forgotten to put that into the solution, as the final where clause I am using is complicated. Have made the adjustment so that it reflects the original question. — Andrew Scott, Sep 13 '20 at 20:09

score 0 · Answer 2 · answered Sep 13 '20 at 04:53

0

You are using += after initially assigning currentTotal

Change it to + like below:

AccountBalance = currentTotal + p.TransCode == "Deposit" ? p.TransactionAmount : -p.TransactionAmount

answered Sep 13 '20 at 04:53

Glynn Hurrell

372
2
8

But then it is not a running total. – Andrew Scott Sep 13 '20 at 05:01
You either want to look at using .Sum() or .ForEach() – Glynn Hurrell Sep 13 '20 at 05:11
So there is no way to do this with Entity Framework? It can be done with normal SQL, I would have thought you could do it with Entity Framework, – Andrew Scott Sep 13 '20 at 05:14
Sorry I should have said, these are Linq methods that you can use with EF. Check out [this SO post](https://stackoverflow.com/questions/14349573/how-to-sum-a-column-in-entity-framework) – Glynn Hurrell Sep 13 '20 at 05:22
Sorry, that is not the same thing. In SQL, I can get a running total of the transactions. like this post [link](https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/) And I am trying to do the same thing with Entity Framework. From what I can tell the post you showed, gets a total and stores it. Not the same thing, that I am trying to do. – Andrew Scott Sep 13 '20 at 05:35
Does [this](https://stackoverflow.com/a/1834940/13436798) answer your question? – Glynn Hurrell Sep 13 '20 at 11:35
I tried that before posting here, and I could not get that to work either. – Andrew Scott Sep 13 '20 at 17:33

Entity Framework getting a running total from columns

2 Answers2