Excluding NULL values when using SUM() in LINQ

Question

I am trying to use SUM() to total all the entries in a column and then convert the number to an int.

I am running into a problem in the conversion because some of the entries are NULL. I tried to exclude them using a != null in a WHERE() clause but I am still getting the same error

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

Here is my LINQ query, can someone point out what I am missing in order to properly exclude nulls?

TotalLiftings = db.Query<Transaction>().
    Where(tr => tr.TerminalId == TerminalUserData.ID && 
        tr.ProductId == t.ProductId && 
        tr.TransactionDate == t.InventoryDate &&
        tr.NetGallons != null).
Select(tr => tr.NetGallons).
Sum();

Have you tried NetGallons.HasValue instead? I am assuming NetGallons is nullable — Zeus82, Aug 13 '14 at 15:08
Are you sure that the null is coming from the sum statement and not the where? I would say you should probably start off the where with checking the tr.TerminalId and tr.ProductId, transactionDate, etc. to make sure none of them are null before any other logic. — Taugenichts, Aug 13 '14 at 15:08
@Taugenichts I know there is data in the system but I did not think that the whole statement might just not return anything. I will investigate the `WHERE()` to see if that is where the problem is. — Matthew Verstraete, Aug 13 '14 at 15:17
Possible duplicate of http://stackoverflow.com/questions/6864311 — Moby Disk, Aug 13 '14 at 15:17
Another concern would be how do you know that TerminalUserData and t are not null? Those would definitely cause some problems if they were null... — Taugenichts, Aug 13 '14 at 15:20
@Taugenichts I have tested for and confirmed that `TerminalUserData` and `t` are not null. I am right now testing out the where clause — Matthew Verstraete, Aug 14 '14 at 20:16

score 1 · Accepted Answer · answered Aug 13 '14 at 15:08

1

You just need to cast to a nullable int. The error is occuring because you are trying to sum no records and the result of that is null, but an int cant be null

.Select(tr => tr.NetGallons).Cast<int?>().Sum(),

or

  .Select(tr => (int?)tr.NetGallons).Sum(),

answered Aug 13 '14 at 15:08

Aducci

26,101
8
63
67

But he filtered and selected datas where `tr.NetGallons != null` – Farhad Jabiyev Aug 13 '14 at 15:12
@FarhadJabiyev, the Sum of zero records is a null value. tr.NetGallons is an `int`, so the `Sum` method has to return an `int`. A null value cant be assigned to an int, so it throws an exception. An int can be compared against a null value, but it will always be true – Aducci Aug 13 '14 at 15:15

Excluding NULL values when using SUM() in LINQ

1 Answers1