How to apply filtering for specific column?

Question

I have query like below:

var query = from operation in dbContext.Operations
select new OperationsDto
{
    Id = o.Id,
    ProcessDate = o.ProcessDate,
    Amount = o.Credit
    //Balance = ...
};

There is no Balance property in Operation entity. I have to calculate it. Balance => (sum of operations' Amount which operation has happened before current operation ProcessDate) + current Amount). For example:

Id      ProcessDate     Amount      Balance
1       2021.02.01      +100$       50 + 100 = +150$ (
2       2021.02.03      -200$       150$ + (-200) = -50$ (this get sum of amount where record's process date before 2021.02.03)
3       2019.01.01      +50$        0 + 50$ = 50$ (because this is first operation. there is not record before 2019.01.01)

I want this in EF Core. I know I can do this using foreach after retrieve data like below:

var operations = query.ToList();
foreach (var operation in operations)
{
    operation.Balance = operations.Where(x => x.ProcessDate < operation.ProcessDate).Sum(o => o.Debit - o.Credit);
}

But I need to calculate in query

You can't really do this in Linq, and your `foreach` loop is horribly inefficient. What you're really trying to do is keep a "running total", so I suggest you go read up on that. You can do it with some raw SQL, or use something like [this](https://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results) after you have ordered the results by date. — DavidG, Oct 06 '21 at 21:13
So you are really talking about the running balance associated with each operation. It seems like the initial balance is set from the first record? — NetMage, Oct 06 '21 at 21:47

score 0 · Answer 1 · answered Oct 07 '21 at 09:34

It may be possible to build a balance within the query, but frankly it would likely be quite inefficient as it would need to re-query against the Operations. Something like:

var query = dbContext.Operations
    .Select(o => new OperationsDto
    {
        Id = o.Id,
        ProcessDate = o.ProcessDate,
        Credit = o.Credit,
        Debit = o.Debit,
        Balance = dbContext.Operations.Where(x => x.Id != o.Id && x.ProcessDate <= o.ProcessDate).Sum(x => x.Credit - x.Debit)
    });

A faster solution would be to build your balance in memory:

var operations = query.OrderBy(o => o.ProcessDate).ToList();
decimal balance = 0;
foreach (var operation in operations)
{
    balance += operation.Credit - operation.Debit;
    operation.Balance = balance;
}

This only works if you are loading the entire range. If instead you wanted to query data after a particular date, you can fetch the initial balance and start from there:

var operations = query
    .Where(o => o.ProcessDate >= startDate)
    .OrderBy(o => o.ProcessDate).ToList();

decimal balance = dbContext.Operations
    .Where(o => o.ProcessDate < startDate)
    .Sum(o => o.Credit - o.Debit); // starting balance.

// I can't check if the above is allowed in EF, may need to do the below:
var sums = dbContext.Operations
    .GroupBy(o => true)
    .Where(o => o.ProcessDate < startDate)
    .Select(o new 
    {
        CreditSum = o.Sum(x => x.Credit),
        DebitSum = o.Sum(x => x.Debit)
    }).Single();
var balance = sums.CreditSum - sums.DebitSum; // starting balance.

foreach (var operation in operations)
{
    balance += operation.Credit - operation.Debit;
    operation.Balance = balance;
}

How to apply filtering for specific column?

1 Answers1