How to increment column with last column value in Lambda or Linq

Question

I am not expert in Linq or Lambda i really need help with this query.

This is like Statement of Account it has debit, credit and balance. however my linq query is wrong i got this result when im executing this query...

result: enter image description here suppose the result is this.

here's my code:

 decimal Balance = 0;
 var result = from a in entities.Payments
              where a.StudentID == ParamStudentID
              select new
              {
                 Date = a.DateAdded,
                 Code = entities.Particulars.Where(p => p.Name == a.PaymentDes).Select(sp => sp.Code).FirstOrDefault(),
                 Particulars = a.PaymentDes,
                 Debit = 0,
                 Credit = a.Amount,
                 Balance = Balance + a.Amount,
                 SyTerm = a.SchoolYear + "-" + a.Term.Trim().Substring(0, 5)
              };

i know this is an easy question but i don't know how to solve. :D anyone can help me.

I think 1322 is wrong in the desired result set. Shouldn't it be 1325? — Giorgos Betsos, Dec 10 '14 at 21:59
What you essentially want is to calculate a running total on an ordered result set. Have a look here http://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results — Giorgos Betsos, Dec 10 '14 at 22:16

score 1 · Answer 1 · answered Dec 10 '14 at 22:13

Hope it helps:

 var result = from a in entities.Payments
          where a.StudentID == ParamStudentID
          select new
          {
             Date = a.DateAdded,
             Code = entities.Particulars.Where(p => p.Name == a.PaymentDes).Select(sp => sp.Code).FirstOrDefault(),
             Particulars = a.PaymentDes,
             Debit = 0,
             Credit = a.Amount,
             Balance = entities.Payments.Where(x => x.StudentID == ParamStudentID).TakeWhile(x => x != a).Sum(x => x.Amount) + a.Amount,
             SyTerm = a.SchoolYear + "-" + a.Term.Trim().Substring(0, 5)
          };

I apologize if I have any syntax error, but I wrote it in Notepad, but you can get the idea :).

How is the ordering by date of transaction taken care of in the above linq query? — Giorgos Betsos, Dec 10 '14 at 22:19
@GiorgosBetsos Since I just used the OP's code, I dont think I would need to take that into account. — Dante, Dec 10 '14 at 22:24

Bradford Dillon · Answer 2 · 2014-12-11T01:04:08.570

As you are using Entity Framework you are dealing with a more restrictive version of LINQ. LINQ is designed around immutable variables. You can get around it with having your mutable variables outside of your query, but with LINQ to Entities that option goes away.

One way of handling it would be to do your same query then loop through it and set the balances.

var result = (from a in entities.Payments
             where a.StudentID == ParamStudentID
             orderby a.DateAdded ascending
             select new
             {
                 Date = a.DateAdded,
                 Code = entities.Particulars.Where(p => p.Name == a.PaymentDes).Select(sp => sp.Code).FirstOrDefault(),
                 Particulars = a.PaymentDes,
                 Debit = 0,
                 Credit = a.Amount,
                 Balance = 0,
                 SyTerm = a.SchoolYear + "-" + a.Term.Trim().Substring(0, 5)
              }).ToList();

double balance = 0;

foreach(var item in result)
{
    balance += item.Credit;
    item.Balance = balance;
}

There are probably other ways to do it all in the LINQ query but I think they will probably be relatively convoluted or make SQL that is pretty weird.

That changes things a bit. You might want to explicitly state that in your original question. — Bradford Dillon, Dec 10 '14 at 22:41

bigtlb · Answer 3 · 2014-12-11T06:14:35.300

0

 decimal balance = 0;  //Change for clarity
 var result = (from a in entities.Payments
              where a.StudentID == ParamStudentID
              select new
              {
                 Date = a.DateAdded,
                 Code = entities.Particulars.Where(p => p.Name == a.PaymentDes).Select(sp => sp.Code).FirstOrDefault(),
                 Particulars = a.PaymentDes,
                 Debit = 0,
                 Credit = a.Amount,
                 SyTerm = a.SchoolYear + "-" + a.Term.Trim().Substring(0, 5)
              }).ToList()
                .Select(r=>new 
                   {
                       Rec = r, 
                       Balance = balance += r.Credit
                   });

NOTE: This only works the first time the query is used. You should probably throw a .ToList() on the end of the query, or not reuse it without reseting the accumulator.

edited Dec 11 '14 at 06:14

answered Dec 10 '14 at 22:20

bigtlb

1,462
9
16

Balance is not exist on current context this is the problem on writing your code sir. – Pseudorandom Dec 10 '14 at 22:32
It most certainly does. You are making an anonymous class, and can call your property whatever you like. The other Balance is the decimal accumulator you defined at the beginning. I was just giving you the specific lines to change. I have updated my answer with your entire code clip, and my suggested changes. – bigtlb Dec 10 '14 at 22:50
As he is working with Entity Framework this method fails. Even if you move the accumulator into the scope of the context, you get an error that Expression Trees can not contain assignment statements. If he was working with LINQ to Objects this works fine. It, however, does not work in LINQ to Entities. – Bradford Dillon Dec 11 '14 at 00:51
Thank you for the clarification. So the initial query can be executed, with a `ToList()` call, and then a second select can be executed to shape the result, and add the accumultaor. – bigtlb Dec 11 '14 at 05:32
I have modified the query to work around the entities Expression Tree issue. – bigtlb Dec 11 '14 at 06:16

How to increment column with last column value in Lambda or Linq

3 Answers3