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?
Best Answer
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'sSUM
returnsnull
whereas c# expect 0.A more general approach is to use
??
which will be translated toCOALESCE
whenever there is a risk that the generated SQL returns an unexpected null:This first casts to
int?
to tell the C# compiler that this expression can indeed returnnull
, even thoughSum()
returns anint
. Then we use the normal??
operator to handle thenull
case.Based on this answer, I wrote a blog post with details for both LINQ to SQL and LINQ to Entities.