Click here to Skip to main content
15,039,244 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello, please help me with c# linq entity framework. I need to convert the following sql query to linq, entity framework:

SELECT AccNo, SUM(Debit) - SUM(Credit) AS Balance FROM  TrxAffair
WHERE (TrxDate > '2021-01-01') GROUP BY AccNo


What I have tried:

public decimal GetPreviousBalance(DateTime trxDate)
        {
            var items = context.TrxAffairs
           .AsQueryable()
            .Where(i => i.TrxDate < trxDate)
            .GroupBy(o => o.AccNo)
            .Select(s => s.Sum(i=>(i.Debit-i.Credit)));

            OnGetPreviousBalance(items.Sum(s=>s.Value));

            return items.Sum(s => s.Value);
        }
Posted
Updated 22-Apr-21 22:23pm
Comments
Richard Deeming 22-Apr-21 3:58am
   
You haven't told us what the problem is with your code.

One obvious question: if you want the total for all records, why are you grouping by AccNo?
diginet0 22-Apr-21 8:37am
   
He is doing a balance per account, so to do that he has to group by Account number
Richard Deeming 22-Apr-21 9:16am
   
But he's summing all of the grouped totals. :)
diginet0 22-Apr-21 8:39am
   
What happen when You tried that code?

1 solution

Well, this:
SQL
SELECT AccNo, SUM(Debit) - SUM(Credit) AS Balance 
FROM  TrxAffair
WHERE (TrxDate > '2021-01-01')
GROUP BY AccNo

is equal to this:
C#
var items = context.TrxAffairs
    .AsQueryable()
    .Where(i => i.TrxDate > trxDate)
    .GroupBy(o => o.AccNo)
    .Select(grp => new {AccNo = grp.Key, Balance = grp.Sum(i=>(i.Debit)) - grp.Sum(i=>(i.Credit)))
    .ToList();
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900