C# – How to perform a SUM() / SUM() in Linq

I’m trying to perform this SQL SELECT in my C# Application:

SELECT SUM( a.VALUE_A) / SUM( a.VALUE_B)
    FROM "TABLE" a
    WHERE DATE_COLUMN = to_date('12/05/2018', 'DD/MM/YYYY');

Using this C# with LINQ code:

dto.day = await database.Where(x => x.DATE_COLUMN.Date == date_filtered)
                        .SumAsync(x => (x.VALUE_A) / (x.VALUE_B));

But it throws an exception saying that the divisor it’s ZERO. So, I tried to handle it adding an ternary IF inside the SumAsync() method:

 dto.day = await database.Where(x => x.DATE_COLUMN.Date == date_filtered)
                         .SumAsync(x => x.VALUE_B == 0 ? 0 : (x.VALUE_A) / (x.VALUE_B));

But the final result differs from the direct SQL result:

SQL gives the correct result, for example: 86.25

The C# gives the incorrect result: 227.87

Please, what am I doing wrong?

Answer

Order of operation is key. Try the following:

var values = database.Where(x => x.DATE_COLUMN.Date == date_filtered)
                     .Select(x => new {x.VALUE_A, x.VALUE_B}).ToList();
dto.day = values.Sum(s => s.VALUE_A) / values.Sum(s => s.VALUE_B);

EDIT:

Just for clarity, here’s the breakdown. In your query, you’re dividing the sum of VALUE_A by the sum of VALUE_B.

In your LINQ statement, you’re summing the results of VALUE_A / VALUE_B of each record.

In my answer, I’m creating an anonymous object where I only get VALUE_A and VALUE_B. Then I take the sum of each and divide them by each other. Hopefully, that’s a better explanation.