How to do an aggregate query in linq

Question

I am new to linq an I need help. I have a transactions table with an amount field and i want split the amount field into Debit and Credit. What is the linq equivalent to the sql query below: Select sum(Amount < 0 ? Amount:0) as Debit, sum (Amount >0 ? 0: Amount) as Credit from transactions where Account = strAccount.

Answer 1

Unfortunately there's no simple way of expressing both aggregations in a single LINQ query like that when it's fetching a single result. The two separate queries would be:

// Doesn't actually perform a query yet
var accountTransactions = db.Transactions.Where(t => t.AccountId == accountId);

var credit = accountTransactions.Sum(t => t.Amount > 0 ? t.Amount : 0);
var debit = accountTransactions.Sum(t => t.Amount < 0 ? t.Amount : 0);

Or alternatively:

var credit = accountTransactions.Sum(t => Math.Max(t.Amount, 0));
var debit = accountTransactions.Sum(t => Math.Min(t.Amount, 0));

I can't guarantee that these will have translations in (say) LINQ to SQL, but I'd hope that they would.

If you were finding the credit / debit for all accounts, you could do that in a single query:

var query = from account in db.Accounts
            join transaction in db.Transactions
              on account.AccountID equals transaction.TransactionID
              into trans
            select new { Account = account,
                         Credit = trans.Sum(t => Math.Max(t.Amount, 0)),
                         Debit = trans.Sum(t => Math.Min(t.Amount, 0)) };

Now of course you could then use:

var myTransactions = query.Where(result => result.Account.AccountId == accountID)
                          .FirstOrDefault();

That then would be a single SQL statement, returning either a single result or null if it can't find that account ID. Again, you'd have to see how it actually translated to SQL.

Answer 2

You could do a statement like:

var query = from t in db.Transactions
            where t.Account == strAccount
            group t by t.Account into grouping
            select new
            {
                Debit = grouping.Sum(x => x.Amount < 0 ? x.Amount),
                Credit = grouping.Sum(x => x.Amount > 0 ? x.Amount),
            };

This translates to SQL as:

SELECT SUM(
    (CASE 
        WHEN [t0].[Amount] < @p1 THEN [t0].[Amount]
        ELSE @p2
     END)) AS [Debit], SUM(
    (CASE 
        WHEN [t0].[Amount] > @p3 THEN [t0].[Amount]
        ELSE @p4
     END)) AS [Credit]
FROM [Accounts] AS [t0]
WHERE [t0].[Account] = @p0
GROUP BY [t0].[Account]

Not quite the same as the original - you'd need to run query analyser in order to understand if the impact of the GROUP BY was significant for your database.

Answer 3

db.Transactions
    .Select(t => new { CR=Math.Min(0,t.Amount), DB=Math.Max(0,t.Amount) })
    .Aggregate(new { CR=0, DB=0 }, (s, t) => return new { CR=s.CR+t.CR, DB=s.DB+t.DB });

This has the benefit of running over the transactions table only once. However, this creates a lot of temp objects, one for each transaction.

If you need to conserve memory usage, then do two separate passes over the transactions:

var cr = db.Transactions.Sum(t => Math.Min(0,t.Amount));
var db = db.Transactions.Sum(t => Math.Max(0,t.Amount));