How to do an aggregate query in LINQ

Question

I have a transactions table with an amount field and I want to 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.