The cast to value type 'Int32' failed because the materialized value is null

Question

I have the following code. I'm getting 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."

when CreditHistory table has no records.

var creditsSum = (from u in context.User
                  join ch in context.CreditHistory on u.ID equals ch.UserID                                        
                  where u.ID == userID
                  select ch.Amount).Sum();

How can I modify the query to accept null values?

Answer 1

A linq-to-sql query isn't executed as code, but rather translated into SQL. Sometimes this is a "leaky abstraction" that yields unexpected behaviour.

One such case is null handling, where there can be unexpected nulls in different places. ...DefaultIfEmpty(0).Sum(0) can help in this (quite simple) case, where there might be no elements and sql's SUM returns null whereas c# expect 0.

A more general approach is to use ?? which will be translated to COALESCE whenever there is a risk that the generated SQL returns an unexpected null:

var creditsSum = (from u in context.User
              join ch in context.CreditHistory on u.ID equals ch.UserID                                        
              where u.ID == userID
              select (int?)ch.Amount).Sum() ?? 0;

This first casts to int? to tell the C# compiler that this expression can indeed return null, even though Sum() returns an int. Then we use the normal ?? operator to handle the null case.

Based on this answer, I wrote a blog post with details for both LINQ to SQL and LINQ to Entities.