Simple math question with LinQ, simple Join query, Nulls, etc.

Question

I have 2 tables
  1. Client
  2. Operations

Operations can result in: Credits or Debits ('C' or 'D' in a char field) along with date and ammount fields.

I must calculate the balance for each client's account using linQ... The result should also show balance 0 for clients whom didn't make operations yet

I have the following function with linQ statement, but I know it could be done in a better and quicker, shorter way, right? Which will be?

public static double getBalance(ref ClasesDeDatosDataContext xDC, string SSN,
int xidClient)
{
    var cDebits =
     from ops in xDC.Operations
                where (ops.idClient == xidClient) && (ops.OperationCode == 'D')
                select ops.Ammount;
    var cCredits =
                from ops in xDC.Operations
                where (ops.idClient == xidClient) && (ops.OperationCode == 'C')
                select ops.Ammount;
    return (double)(cCredits.Sum() - cDebits.Sum());
}

Thanks !!!

Answer 1

I don't know if the LINQ-to-SQL engine can handle the expression, but something like this should be possible:

return (
  from ops in xDC.Operations
  where ops.idClient == xidClient
  select ops.operationCode == 'C' ? ops.Amount : -ops.Amount
).Sum(a => a);

Or perhaps:

return (
  from ops in xDC.Operations
  where ops.idClient == xidClient
  select new { Sign = ops.operationCode == 'C' ? 1.0 : -1.0, Amount = ops.Amount }
).Sum(o => o.Sign * o.Amount);

If the collection is empty, the Sum method returns zero, so that takes care of clients without transactions.

Edit:
Corrected spelling in query: Ampont -> Amount