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?
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.