CODEWITHSUNDEEP
sql-servervb.netentity-frameworklinq
Najam
Najam

Reputation: 152

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

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:

enter image description here

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

Upvotes: 2

Views: 209

Answers (2)

Najam
Najam

Reputation: 152

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.

Upvotes: 0

Sherman
Sherman

Reputation: 853

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

Upvotes: 2

Related Questions