CODEWITHSUNDEEP
c#sqlentity-frameworklinq
WitnessTruth
WitnessTruth

Reputation: 579

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?

Upvotes: 0

Views: 2080

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205629

You are getting wrong result because Sum(a) / Sum(b) is not the same as Sum(a / b). What you want is

(a1 + a2 + ... aN) / (b1 + b2 + ...bN)

while what you get is

(a1 / b1 + a2 / b2 + ... aN / bN)

So to get the desired result, in LINQ you'd normally need 2 Sum calls on the result of GroupBy or similar set. Since your query is a top level, you can either execute 2 database Sum queries and calculate the result from them, or use the fake group by constant trick to get it with a single database query. Something like this:

var query = db.Table
    .Where(e => e.DATE_COLUMN.Date == date_filtered)
    .GroupBy(e => 1) // <--
    .Select(g => new
    {
        SumA = g.Sum(e => e.VALUE_A), // <--
        SumB = g.Sum(e => e.VALUE_B), // <--
    })
    .Select(e => e.SumB == 0 ? 0 : e.SumA / e.SumB);

var result = await query.FirstOrDefaultAsync();

Upvotes: 2

Blue Eyed Behemoth
Blue Eyed Behemoth

Reputation: 3872

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.

Upvotes: 4

Related Questions