C# LINQ expression cannot be translated when summing group joins

Question

I have two tables: users and transactions. The transactions table stores user wallet transactions (+ balance, - balance) etc. I'm trying to obtain a list of users based on the current balance of their wallet ordered by largest first. (I can get the total balance by summing the Amount column in the Transactions table).

var query = from u in _context.users
                        join t in _context.Transactions on u.id equals t.UserId into gj
                        from txn in gj.DefaultIfEmpty()
                        where txn.TransactionStatus == Transaction.Status.Success && !u.Deleted.HasValue
                        select new
                        {
                            balance = gj.Sum(a => a.Amount),
                            user = u,
                        };

            var result = await query.OrderByDescending(t => t.balance).Skip(offset).Take(range).ToListAsync();

I am getting the error:

The LINQ expression 'gj' could not be translated.

This is the equivalent SQL I'm trying to achieve:

SELECT balance, u.* FROM

(SELECT COALESCE(SUM(Amount), 0) as balance, u.id
  FROM dbo.users u
    LEFT JOIN dbo.Transactions t ON(u.id = t.UserId AND t.TransactionStatus = 0)
  WHERE u.Deleted IS NULL
  GROUP BY u.id) as tbl

JOIN dbo.users u ON(u.id = tbl.id)
ORDER BY balance DESC

user_1410664 · Answer

Starting from users and using the navigation props to go through to Trans should make this trivial. Give something like this a go:

users.Where(u => u.Deleted == null)
.Select(u => new {
    User = u,
    Balance = u.Transactions.Where(t => t.Status == 0).Sum(t => t.Amount)
})
.OrderByDescending(at => at.Balance);

If the logic truly is "list all users but only add up transactions for users that are non deleted, and show deleted users as 0 balance" then:

users.Where(u => u.Deleted == null)
.Select(u => new {
    User = u,
    Balance = u.Deleted != null ? 0 : u.Transactions.Where(t => t.Status == 0).Sum(t => t.Amount)
})
.OrderByDescending(at => at.Balance);

Try not to write EF queries like "right, in SQL I would have this table and join that table, and it'd be a left join, and that would be summed... so I'll tell EF to do this context set join that context set, and it's a left join, and sum..." - EF will write joins for you; express your requirements in terms of how you want the C# object graph to be manipulated and let EF do the conversion to SQL how it can; use the navigation props between entities so it can work out how you want to bring your data together and arrange the necessary joins. It seldom needs micromanaging in a SQL-ey flavored approach

C# LINQ expression cannot be translated when summing group joins

1 Answers