Grouping and Sum Linq to Entities

linq, linq-to-entities, sql

Solution

The following should be a comparable C# LINQ implementation:

class AccountTransaction
{
    public int FinancialAccountId { get; set; }
    public bool LedgerVisible { get; set; }
    public decimal CreditAmount { get; set; }
    public decimal DebitAmount { get; set; }
}

var transactions = new List<AccountTransaction>();

var results = from at in transactions
              where at.FinancialAccountId == 4 && at.LedgerVisible == true
              group at by at.FinancialAccountId into g
              select new
              {
                  FinancialAccountId = g.Key,
                  Delta = g.Sum(x => x.CreditAmount) - g.Sum(x => x.DebitAmount)
              };

Problem

I have the following SQL Query and I have tried to convert it to LINQ but without any success: ``` select at.financialaccountid, SUM(creditamount)-SUM(debitamount) from account_transactions at where at.financialaccountid = 47 and ledgervisible = 1 GROUP BY at.financialaccountid ``` Hope someone can guide me on this. Thanks.

Original source