IQueryable that sums the amount where two unique id's match C#

Question

I have a table in which I am querying. Each payment has a total number and a negative number representing a fee. At the moment my query pulls the needed query without the negative number.

With the change I recently got I need to instead of just select the positive numbers I need to add the positive and negative numbers together where the batchId's match in the single table.

I have been working on it for a couple hours and everything I have tried I end up summing everything in the query and not just the negative and positive numbers where the batch id's match.

Please help. Below is my Query, it's the amount column I want to sum.

Example data on the left, what i want on the right

batchId Amount      batchId Amount
1       2.23        1       1.00
1      -1.23        2       3.00
2       5.43m       3       4.00
2      -2.43
3       6.60
3      -3.60

IQueryable<BatchPayment> query = _db
                .BatchPayments
                .Where(
                    bp => ValidBatchStatuesIds.Contains(bp.Batch.BatchStatusId)
                        && (!OnlineBatchTypes.Contains(bp.Batch.BatchTypeId)
                            || (OnlineBatchTypes.Contains(bp.Batch.BatchTypeId) && bp.Amount > 0.00m))
                );

It is not clear what does `add the positive and negative numbers together where the batchId's match in the single table` mean. Can you clarify it or even better show us some sample data and the expected result? Because as of now it seems that you just need `var sum = _db...Where...Select(b => b.Amount).Sum()`. — Eugene Podskal, Jan 28 '17 at 18:35
that is the original data in the database that is already spit out by the query. — BlowFish, Jan 28 '17 at 19:02

score 0 · Answer 1 · edited May 23 '17 at 11:45

If I understand you right, you need to do two things:

Get all batchIds that conform to that rule
Get sum of all(whether conforming or not) payments with such batchIds.

It can be done like:

var query = _db
    .BatchPayments
    .Where(
        bp => ValidBatchStatuesIds.Contains(bp.Batch.BatchStatusId)
            && (!OnlineBatchTypes.Contains(bp.Batch.BatchTypeId)
                || (OnlineBatchTypes.Contains(bp.Batch.BatchTypeId) && bp.Amount > 0.00m))
    )
    // Get batchIds that have payments conforming to the rule.
    .Select(bp =>
        bp.batchId)
    // Remove duplicate batchIds
    .Distinct()
    // Select each and every BatchPayment with previously found batchIds
    .SelectMany(batchId =>
        _db
            .BatchPayments
            .Where(bp =>
                bp.batchId == batchId))
    // Group found batchPayments
    .GroupBy(bp =>
        bp.batchId)
    // Get sum for each batchId
    .Select(gr =>
        new
        {
            batchId = gr.Key,
            Amount = gr.Sum(i => i.Amount)
        });

Hmm, though theoretically .SelectMany(batchId => _db.BatchPayments may not exactly work, so you'd be better with Join instead(and it may be even more idiomatic):

var query = _db
    .BatchPayments
    // Join to the self...
    .Join(
        // ...where all improper batchIds are already filtered out
        _db
            .BatchPayments
            .Where(
                bp => ValidBatchStatuesIds.Contains(bp.Batch.BatchStatusId)
                    && (!OnlineBatchTypes.Contains(bp.Batch.BatchTypeId)
                        || (OnlineBatchTypes.Contains(bp.Batch.BatchTypeId) && bp.Amount > 0.00m))),
        sc => sc.batchId,
        soc => soc.batchId,
        (sc, soc) => sc)        
    // Group found batchPayments
    .GroupBy(bp =>
        bp.batchId)
    // Get sum for each batchId
    .Select(gr =>
        new
        {
            batchId = gr.Key,
            Amount = gr.Sum(i => i.Amount)
        });

score 0 · Accepted Answer · answered Jan 28 '17 at 19:06

Give it a try (For correct validations I'm skipping the irrelevant conditions):

var result = _db.BatchPayments
                 .GroupBy(p=> p.BatchId)
                 .Select(p=> new { BatchId = p.Key, Amount = p.Sum(q=> q.Amount)})
                 .ToList();
foreach (var batch in result)
{
    Console.WriteLine("BatchId = {0} Amount={1}", batch.BatchId, batch.Amount);
}

IQueryable that sums the amount where two unique id's match C#

2 Answers2