Because the materialized value is null, the cast to value type 'Int32' failed.

.net c# entity-framework linq-to-entities

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?

1
191
11/18/2013 8:42:26 PM

Accepted 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'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.

323
8/8/2012 3:11:49 PM

Popular Answer

To allow a nullable Amount field, just use the null coalescing operator to convert nulls to 0.

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 ?? 0).Sum();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow