如何在 LINQ 中进行聚合查询

Question

我有一个带有金额字段的交易表，我想将金额字段拆分为借方和贷方。与下面的 SQL 查询等效的 LINQ 是什么？

Select sum(Amount < 0 ? Amount : 0) as Debit, sum(Amount > 0 ? 0 : Amount) as Credit
from transactions
where Account = strAccount

Answer 1

不幸的是，没有像获取单个结果时那样在单个 LINQ 查询中表达两种聚合的简单方法。这两个单独的查询将是：

// 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);

或者：

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

我不能保证这些查询将在（例如）LINQ to SQL 中进行翻译，但我希望它们会如此。

如果您要查找所有帐户的贷方/借方，您可以在单个查询中执行此操作：

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)) };

现在当然您可以使用：

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

那么会< /em> 是单个 SQL 语句，返回单个结果，如果找不到该帐户 ID，则返回 null。同样，您必须了解它实际上如何转换为 SQL。

Answer 2

您可以执行如下语句：

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),
            };

这转换为 SQL 为：

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]

与原始的不太一样 - 您需要运行查询分析器才能了解 GROUP BY 的影响是否对你的数据库。

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 });

这样做的好处是只需在事务表上运行一次。但是，这会创建大量临时对象，每个事务对应一个临时对象。

如果您需要节省内存使用量，请对事务执行两次单独的传递：

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