The Cast to Value Type 'Int32' Failed Because the Materialized Value Is Null

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

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.

The cast to value type 'System.Int32' failed because the materialized value is null. But it works on similar code

I think the problem is that you are calling .Value on your nullable int without checking it for null first. Ultimately it means you are performing a narrowing implicit conversion in the middle of the query.

If the code were to be run in .NET, and the x.MyOtherForeignKey was null, it would throw a Nullable object must have a value exception. In this case, the code is being converted into a Linq-to-Sql expression and run in EntityFramework. I think it's finding a null x.MyOtherForeignKey in your second query and giving this exception.

Depending on what you want the code to do when it encounters a null, you could change it to filter out the nulls:

var lstOfIntsINeed =
db.AssociationTable.Where(
x => x.PropertyId == picId && x.MyOtherForeignKey != myOtherId && x.MyOtherForeignKey != null)
.Select(x => x.MyOtherForeignKey.Value)
.ToList();

... or to replace the null with a default value.

I think the same problem exists in your first query as well, and the only reason that it didn't fail in your tests is because the query returns no entries (or more specifically, no entries with a null MyForeignKey), so it hasn't failed.

Given that you've defined MyForeignKey as nullable, you must be considering a situation where it is null, so it would be sensible to fix it in that query too.

You could test to see if this was the case by changing the .Where() clause to deliberately return AssociationTable entries with null values.

Hope this helps

LINQ: failed because the materialized value is null

You need allow a nullable Quantity, You can achieve it using ?? expression and cast to int? when you use Sum().

.Sum(x => (int?)x.Quantity)??0

Your query should look like

var fullInfo = (from product in allProdcts
let quantity = db.Product_Order.Where(x => x.ProductId == product.ID).Sum(x => (int?)x.Quantity)??0
select new ReportVm
{
ProductId = product.ID,
ProductName = product.Name,
AmountProduct = quantity,
TotPrice = (quantity)*(product.Price)
}).ToList();

Error: The cast to value type 'System.Int32' failed because the materialized value is null

Modify it using DefaultIfEmpty(), check here

Modified Query

var lastTransactionId = db.Transactions.Where(w => w.AccountId == accountInstance.Id && w.IsCancelled == false && w.TransactionTypeId == 2 && w.Date.Year >= 2015)
.Select(t=>t.Id)
.DefaultIfEmpty(-1)
.Max()

You can define the value need to returned, if the collection is empty, like I have made it -1, else it will be default value

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

This error is caused by a situation where the query result type has a column/property of non-nullable type but the generated query results in a NULL value.

This could be considered a bug or not. It is hard to see what the L2S team should have done differently here. I think they should have added a better error message. This bug is insidious because it sometimes only strikes in production under unusual data...

Your left join (sd) seem not to match and one of the sd.* properties that you select must be an int. Solve that like this:

 DeptNo = (int?)sd.DepartmentID, //Cast to nullable

d.CategoryID ?? 0

What are you doing here? This seems to be a way to make the join compile. It's better to use:

                    join r in camOnlineDb.Reasons
on new { a = d.ClientID, b = (int?)d.ReasonID }
equals new { a = r.ClientID, b = (int?)r.ReasonID }

This cast makes the anonymous type signatures compatible. The generated SQL should now be faster. If you say x ?? 0 that converts to COALESCE(x, 0) which can prevent index use and such.

Join DefaultIfEmpty() Error: The cast to value type 'System.Int32' failed because the materialized value is null

I thought with a join (left) and DefaultIfEmpty() it would solve this.

It would fix it, but the corresponding properties must be nullable, in this case ImageId must be a nullable property.

In your class change ImageId to a nullable int with ?;

public int? ImageID {get;set;}

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

The problem is that the view model Price property type is non nullable, but since the source InventoryLine is optional, EF (as suggested in the exception message) needs to be able to store a nullable value when the source is null.

You can fix it in two ways:

(A) Make the view model property nullable:

public class InventoryLineViewModel
{
public decimal? Price { get; set; }
}

(B) Keep the view model and change the mapping as follows:

.ForMember(d => d.Price, o => o.MapFrom(s => ((decimal?)s.InventoryLine.Price) ?? 0))

or

.ForMember(d => d.Price, o => o.MapFrom(s => s.InventoryLine != null ? s.InventoryLine.Price : 0))

The cast to value type 'Int32' failed

It sounds like your model defines one of the properties as non-nullable, but the database has the corresponding column as nullable, and has a null value in it. When the generated reader attempts to populate the model, BOOM. Unfortunately, we can't tell you which property, and it doesn't need to even be one of those mentioned in that code, since it could be the FrobNumber property of whatever Sales_inv_details represents.

You need to check your model very carefully. Start with the properties / columns on whatever Sales_inv_details represents. When you have found a mismatch: mark it as nullable, and retry.



Related Topics



Leave a reply



Submit