Exclude null values while summation by specific column in a left joined table using LINQ

Question

I have read a few answers on SO regarding but I think they don't address my problem. I would be happy if someone can point me to one that is relevant. Consider the following tables:

There can be various rates at which a booking may be charged and there can be multiple or no charges per booking. Each charge has its own rate (in $). So e.g. a vehicle booked can be charged for Late Arrival at $200 and for another charge No Show at $450. These rates can vary for every booking and not all bookings have charges.

Now I want to show the total amount charged on bookings if there is any. For that, I have the following LINQ query:

Dim q = (From b In _db.Bookings
           Select New With {
                .Bid = b.ID,
                <some other columns from booking table>
                .TotalCharges = b.BookingCharges.Sum(Function(o) o.Rate)
           }).ToList()

The query returns error:

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

Since there are bookings that do not have any charges (NULL) and hence cannot be summed. I have tried to use .TotalCharges = b.BookingCharges.DefaultIfEmpty().Sum(Function(o) o.Rate) and .TotalCharges = b.BookingCharges.Sum(Function(o) o.Rate or 0) but same error. I have read answers on SO related to this error but I feel the OP has a different problem or query than mine, and also the answers are all C#.

May be I need something equivalent of SQL's COALESCE(SUM(Rate), 0) in LINQ. I used a utility that converts SQL to LINQ but it just did IF Statement, even that is not yielding the result, giving the same error. — Najam, Dec 15 '18 at 18:32

Sherman · Answer 1 · 2018-12-15T17:53:12.720

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()

edited Dec 15 '18 at 17:53

answered Dec 15 '18 at 17:46

Sherman

853
5
16

Compile Time Error: BC33107 First operand in a binary 'If' expression must be nullable or a reference type. Should I have to make Rate property nullable? How? – Najam Dec 15 '18 at 17:57
On your edit, I got this: Error BC31419 'IsNot' requires operands that have reference types, but this operand has the value type 'Integer'. I tried `Function(r) Not IsNothing(r.Rate)` then this error: LINQ to Entities does not recognize the method 'Boolean IsNothing(System.Object)' method, and this method cannot be translated into a store expression. – Najam Dec 15 '18 at 18:14
What data type is `Rate` in your model? – Sherman Dec 15 '18 at 18:20
Can there be a row in the `BookingCharge` table with a null `Rate`? How is that column defined in the database table? Also, do you actually have records in the `BookingCharge` table? – Sherman Dec 15 '18 at 18:45
It does not allow null in database definition. I changed it to allow null values and at database level, it is accepting null values. I updated the model where under properties of Rate, I can see that `Nullable` property has changed to `None` but the error remains the same. In Model, I changed `Nullable` to `True`, even then the error is same. So I guess it is not due to column Rate accepting null values but because there is **no record** for some bookings since it's left join not an inner join. – Najam Dec 15 '18 at 18:56

score 0 · Answer 2 · answered Dec 15 '18 at 19:19

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.

Exclude null values while summation by specific column in a left joined table using LINQ

2 Answers2