Solved "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 Using LINQ

I love LINQ! Do you know that about me? Actually, I REALLY LOVE LINQ! It is so useful for so many different queries. It really eliminates the need to have query code outside of your application sitting in a database. Of course, you have to make sure that your queries are generating good SQL, but it is really useful and a beautiful tool. With that said, you can run into moments when LINQ generates errors that don't offer immediate information on how to solve them. One of my queries generated an error. The fix was extremely simple so I thought I would include it here for anyone that has the same error.


When you are trying to use an aggregation (i.e. sum, max, min, etc), you might come across this issue. The issue is that you might have a time when you have no records in your database. Therefore, something like a sum would return null. What is a programmer to do? Ooh I know, I know! Use DefaultIfEmpty(0)!


There you have it! Problem solved! Now say some nice things about me in the comments....or not.


Smooches,


Kila Morton


*******************************************************************************


PROBLEM:


When using a LINQ query, you get the following 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

Example bad query -

var iAddToValue = (from prod in db.Lists.Where(x => x.AccountId == list.AccountId)

                                     select prod.DisplayOrder)

                                     .Max() + 1;

SOLUTION
Add a DefaultIfEmpty(0) to your query.


Example good query -

var iAddToValue = (from prod in db.Lists.Where(x => x.AccountId == list.AccountId)
                                     select prod.DisplayOrder).DefaultIfEmpty(0)
                                     .Max() + 1;