Linq where clause - comparing integers

Question

This one does not work:

var queryEH = from eh in entity.EmployeesHires where eh.ParentKey == item.PPYKey select eh;
foreach (var itemEH in queryEH)
{
   var query = (from el in entity.EmployeeLeaves where el.HireID == itemEH.ID select el.Duration).Sum();
}

whereas this one does:

var queryEH = from eh in entity.EmployeesHires where eh.ParentKey == item.PPYKey select eh;
foreach (var itemEH in queryEH)
{
   var query = (from el in entity.EmployeeLeaves where el.HireID == 125 select el.Duration).Sum();
}

The exception on the first one is:

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.

possible duplicate of [The cast to value type 'Int32' failed because the materialized value is null](http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) — Asad Saeeduddin, May 13 '14 at 17:32
The issue isn't LINQ per se. It's LINQ to SQL, since you're using Entity Framework. — Asad Saeeduddin, May 13 '14 at 17:34
That doesn't actually answer my question about the type of itemEH.ID... — Jon Skeet, May 13 '14 at 17:37
I'm pretty sure the difference is that there are no matches for `itemEH.ID`, so you're summing over an empty sequence. This produces NULL in SQL, whereas `Sum` needs to return an `int` (and `null` is not assignable to `int`). This is all covered in the accepted answer to the question I linked. — Asad Saeeduddin, May 13 '14 at 17:40
@Asad the link you pasted sorted the issue, when the total is empty, it fails. I would never think of Sum() ?? 0. Where do people pick these kind of syntax? — Jude, May 13 '14 at 17:44
@Jude Like I said, the problem is when you sum over an empty sequence, not when the sum is 0. If you had like fifteen matches and all of them had a value of 0, you would get a sum of 0 with no problems. `??` is the null coalesce operator. It turns your problematic null into 0, and everything is hunky dory. — Asad Saeeduddin, May 13 '14 at 17:46

score 0 · Answer 1 · answered May 13 '14 at 19:22

This is because in some cases the statement

(from el in entity.EmployeeLeaves 
 where el.HireID == itemEH.ID select el.Duration).Sum()

returns a null value, because there are no matches. But the Sum() method is designed to return a (non-nullable) integer, so the conversion to the return value fails.

This happens because the whole statement is executed in SQL and SQL returns nothing when there is nothing to sum. The only thing the C# code is aware of is the returned null value.

This is different in LINQ to objects:

Enumerable.Empty<int>().Sum()

kindly returns 0 because the Sum extension method is implemented to return 0 when the collection is empty.

Of course you shouldn't fix this by switching to LINQ to objects. you can simply do this:

(from el in entity.EmployeeLeaves 
 where el.HireID == itemEH.ID select el.Duration)
    .DefaultIfEmpty() // <= "inserts" a `0` when there is no result
    .Sum()

Linq where clause - comparing integers

1 Answers1