Click here to Skip to main content
15,503,367 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 23: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();
 
Share this answer
 

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