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.

Answer 2

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();

Answer 3

I have used this code and it responds correctly, only the output value is nullable.

var packesCount = await botContext.Sales.Where(s => s.CustomerId == cust.CustomerId && s.Validated)
                                .SumAsync(s => (int?)s.PackesCount);
                            if(packesCount != null)
                            {
                                // your code
                            }
                            else
                            {
                                // your code
                            }

Answer 4

Had this error message when I was trying to select from a view.

The problem was the view recently had gained some new null rows (in SubscriberId column), and it had not been updated in EDMX (EF database first).

The column had to be Nullable type for it to work.

var dealer = Context.Dealers.Where(x => x.dealerCode == dealerCode).FirstOrDefault();

Before view refresh:

public int SubscriberId { get; set; }

After view refresh:

public Nullable<int> SubscriberId { get; set; }

Deleting and adding the view back in EDMX worked.

Hope it helps someone.

Answer 5

You are using aggregate function which not getting the items to perform action , you must verify linq query is giving some result as below:

var maxOrderLevel =sdv.Any()? sdv.Max(s => s.nOrderLevel):0

Answer 6

I see that this question is already answered. But if you want it to be split into two statements, following may be considered.

var credits = from u in context.User
              join ch in context.CreditHistory 
                  on u.ID equals ch.UserID                                        
              where u.ID == userID
              select ch;

var creditSum= credits.Sum(x => (int?)x.Amount) ?? 0;

Answer 7

Got this error in Entity Framework 6 with this code at runtime:

var fileEventsSum = db.ImportInformations.Sum(x => x.FileEvents)

Update from LeandroSoares:

Use this for single execution:

var fileEventsSum = db.ImportInformations.Sum(x => (int?)x.FileEvents) ?? 0

Original:

Changed to this and then it worked:

var fileEventsSum = db.ImportInformations.Any() ? db.ImportInformations.Sum(x => x.FileEvents) : 0;

Answer 8

I was also facing the same problem and solved through making column as nullable using "?" operator.

Sequnce = db.mstquestionbanks.Where(x => x.IsDeleted == false && x.OrignalFormID == OriginalFormIDint).Select(x=><b>(int?)x.Sequence</b>).Max().ToString();

Sometimes null is returned.