LINQ SUM Nullable

Question

I have a simple linq query

(From a In db.payments Group Join b In db.interestcharges On  a.pid Equals b.pid Into totalinterest = Group, TotalInterestReceived = Sum(b.interest)) select a, TotalInterestReceived).toList()

b.interest is type decimal in DB.

Throws

"The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."

This is because InterestCharges table may not have any interest records for pid.

I have tried sum(if(b.interest=nothing,0,b.interest) but this is translated by LINQ to if(b.interest=0, 0, b.interest) hence it never checks for null. Nothing else seems to work instead of nothing. I have tried vbNull, isDBNull(), no success. query works fine when the sum is not null. defaultifempty(0) may work but not sure how to use it in this scenario. Any pointers?

Does using Option Strict On give any useful hints to correct anything? Also, to compare to `Nothing`, the operator is `Is`, not `=`. — Andrew Morton, Sep 22 '18 at 18:47
Cast `b.interest` here `Sum(b.interest)` to nullable `Decimal`, then apply conditional operator to assign 0 to `Sum(…)` result if null. In C# it would be `Sum((decimal?)b.interest) ?? 0`, I don't know the VB syntax, hope you could translate it. — Ivan Stoev, Sep 22 '18 at 19:03
@AndrewMorton `is nothing` doesn't work in LINQ query, syntax error. — Allen King, Sep 22 '18 at 19:40
@IvanStoev compiler wouldn't allow cast from decimal to decimal? — Allen King, Sep 22 '18 at 19:56
@AllenKing Why not? Every value type `T` can be cast (converted) to `Nullable`. This is from CLR, not language specific. — Ivan Stoev, Sep 22 '18 at 20:00
@IvanStoev I guess because `b.interest` is not nullable field in EntityFramework definitions hence compiler wouldn't allow it to be `DirectCast` to Decimal? — Allen King, Sep 22 '18 at 20:04
@AllenKing Perhaps one of the answers at [Nullable property to entity field, Entity Framework through Code First](https://stackoverflow.com/q/10710393/1115360) would help. — Andrew Morton, Sep 22 '18 at 20:25

Stephen Wrighton · Accepted Answer · 2018-09-24T15:00:05.713

The GROUP JOIN statement indicates that you're attempting a LEFT OUTER JOIN. This is why you're receiving a NULL issue. By using JOIN for an INNER JOIN, then you won't stumble on this, but it also means that you will only see those items that have values for interest.

(FROM a in db.Payments 
Join b in db.InterestCharges ON a.pid Equals b.Pid 
SELECT a, TotalInterestReceived = SUM(b.Interest) 
).toList()

It is possible to generate a sub-select which may get the values you're hoping for. The purpose here is that you get all payments, and then basically add in the Interest charges (or 0 if there are none).

(From a In db.Payments 
Select a 
, TotalInterestRecieved = (From b in db.InterestCharges 
                           Where b.pid = a.pid
                           select b.Interest).DefaultIfEmpty(0).Sum() 
).ToList

EDIT:

Another option would be to bypass EF entirely. Build a view in the database and query that view directly rather than attempting to access the underlying data via LINQ.

Most other suggestions I would have would involve iterating through the initial list of "Payments" and populating the values as needed. Which is fine for a small number of "Payments" but that is a O(n) solution.

The actual query is more complicated hence I can't use `.defaultifempty(0).sum()`, it has to be `sum(xxxxxx)`. Also I can't use inner join because the second table may not have records matching the first table, hence it will limit he resultset to what exists in the second table. — Allen King, Sep 24 '18 at 04:58

LINQ SUM Nullable

1 Answers1