score:2

Try using The If() operator:

Dim q = (From b In _db.Bookings
    Select New With {
        .Bid = b.ID
        .TotalCharges = b.BookingCharges.Sum(Function(o) If(o.Rate,0))
}).ToList()

If that doesn't work, maybe try something like this:

Dim q = (From b In _db.Bookings
    Select New With {
        .Bid = b.ID
        .TotalCharges = b.BookingCharges.Where(Function(r) r.Rate IsNot Nothing).Sum(Function(o) o.Rate)
}).ToList()

score:0

I found the solution. Applied IF on whole new column not just on the specific column in the joining table (Rate Column) because the NULL is not originated from the Rate column, it is originating from an empty BookingCharge record against a Booking (attached to the query as a column .TotalCharges) therefore whole column should undergo the IF statement (COALESCENCE).

.TotalCharges = If(b.BookingCharges.Sum(Function(o) o.Rate) <> Nothing, b.BookingCharges.Sum(Function(o) o.Rate), 0)

Thanks to Namrehs and this SO answer.


Related Articles