CODEWITHSUNDEEP
c#linqlambda
Pseudorandom
Pseudorandom

Reputation: 726

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

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. enter image description here

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.

Upvotes: 0

Views: 1168

Answers (3)

bigtlb
bigtlb

Reputation: 1572

 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.

Upvotes: 0

Bradford Dillon
Bradford Dillon

Reputation: 1800

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.

Upvotes: 0

Dante
Dante

Reputation: 3316

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 :).

Upvotes: 1

Related Questions