Calculate Running Total in Linq

Question

I am trying to calculate the balance of my two columns. I tried many ways.But no luck.

Eg:

I want to get a result like this:

Debit    Credit     Balance
=====     ======     =======
125.00     0.00     125.00
236.00     0.00    361.00
0.00    100.00     261.00

My code

var filteredSales = (from av in db.AccountVouchers
            join l in db.Ledgers on new {LedgerID = (Int32) av.LedgerID} equals new     {LedgerID = l.LedgerID}
            join sm in db.SalesMasters on av.VoucherNo equals sm.BillNo.ToString()
            where (av.VoucherDate >= FromDate && av.VoucherDate <= ToDate && av.LedgerID == LedgerID)
            group new {av, l, sm} by new
            {
                av.VoucherDate,
                l.LedgerName,
                av.VoucherType,
                av.VoucherNo,
                sm.REFNO,
                av.Debit,
                av.Credit,
                av.Narration
            }
            into g
            select new
            {

                g.Key.VoucherDate,
                g.Key.LedgerName,
                g.Key.VoucherType,
                VoucherNo = (g.Key.VoucherType != "SALES" ? g.Key.REFNO : g.Key.VoucherNo),
                //g.Key.VoucherNo,
                g.Key.Debit,
                g.Key.Credit,
                g.Key.Narration,
                dSum = g.Sum(s => s.av.Debit),

                //Balance=g.Key.Debit-g.Key.Credit,

              //  TBal = int.Parse(TBal) + (g.Key.Debit != 0 ? TBal + g.Key.Debit : TBal - g.Key.Credit))


                             }).ToList();

what do you mean about `Caculate balance`? You just show the result you want — Tim.Tang, Sep 04 '14 at 06:44
What do you want to do? A running total? Or do you need to update records as well? — flindeberg, Sep 04 '14 at 07:14

score 1 · Answer 1 · answered Sep 04 '14 at 07:07

finally, I understand your question:

void Main()
{
    var list=new List<test>
    {
      new test{ Debit=125, Credit=0},
      new test{ Debit=236,Credit=0},
      new test{ Debit=0, Credit=100},
    };
    if(list.Any())
    {
       var first = list.First();
       first.Balance = first.Debit - first.Credit;
       list.Aggregate((x,y)=>{ y.Balance = x.Balance + y.Debit - y.Credit; return y;});
    }

    list.Dump();
}
class test
{
   public int Debit {get;set;}
   public int Credit {get;set;}
   public int Balance {get;set;}
}

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

This shows an alternate syntax to Tim's example.

void Main()
{
    var list=new List<test>
    {
      new test{ Debit=125, Credit=0},
      new test{ Debit=236,Credit=0},
      new test{ Debit=0, Credit=100},
    };
    int balance = 0;
    list = list.Select( i=> { 
        balance += i.Debit - i.Credit;
        i.Balance = balance;
        return i;
        }).ToList();
    list.Dump();
}
class test
{
   public int Debit {get;set;}
   public int Credit {get;set;}
   public int Balance {get;set;}
}

This is taken from the answer here which is doing a running total. As it mentions in the comments, you should be careful that you don't cause the execution to happen multiple times as the balance value will be off. Using ToList() should take care of that.

edited May 23 '17 at 10:26

Community

1
1

answered Sep 04 '14 at 06:48

Kevin Harker

386
1
7

4

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – jww Sep 04 '14 at 07:12
Thanks for the feedback jww, I updated my answer to include a code sample. – Kevin Harker Sep 04 '14 at 07:26

score 0 · Answer 3 · answered May 11 '18 at 10:13

Simpler Solution :

var result=accountVouchers.Select((x, i) => new AccountVoucher { Debit = x.Debit, Credit = x.Credit, Balance = list.Take(i+1).Sum(y => y.Debit - y.Credit) });

Complete code :

using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var accountVouchers= new List<AccountVoucher>
            {
                new AccountVoucher{ Debit=125, Credit=0},
                new AccountVoucher{ Debit=236,Credit=0},
                new AccountVoucher{ Debit=0, Credit=100},
            };
            var result=accountVouchers.Select((x, i) => new AccountVoucher { Debit = x.Debit, Credit = x.Credit, Balance = list.Take(i+1).Sum(y => y.Debit - y.Credit) });
        }
    }
    class AccountVoucher
    {
        public int Debit { get; set; }
        public int Credit { get; set; }
        public int Balance { get; set; }
    }
}

Calculate Running Total in Linq

3 Answers3