LINQ query returns error "The expected type was 'System.Int32' but the actual value was null."

Question

So this is the query:

from c in Contents
join cs in DepartmentSharings 
on c.ContentId equals cs.ContentId
select c.PrivateCategoryId.Value

When I run this I get:

A database operation failed while processing the request. InvalidOperationException: An exception occurred while reading a database value for property 'DepartmentSharing.ContentId'. The expected type was 'System.Int32' but the actual value was null.

Now I checked the DepartmentSharings.ContentId field in the database, its fk, int, not null. In the class it's public int ContentId { get; set; }. DepartmentSharing.ContentId cannot be null. Also, in LinqPad I can see it returns 58 rows before failing. Why am I getting this error?

Answer 1

The only thing that will cause that error is if ContentId is a non-nullable int property, but somehow your database table is allowing NULL values for that column. Assuming EF is handling your database, that shouldn't happen, but it's possible you've changed something at some point and didn't migrate properly.

Regardless, you need to either change the property type to int? instead of int or alter the table to make the column NOT NULL and ensure all the rows have a non-NULL value.

Answer 2

Chris's answer looks sensible and correct but I have another experience.

I have two tables, they are related. The field with problem is match (not a key but like a key) field. Property is set non-nullable and related database field is same too. The problem occured because of there was no any related data with first table in second table. So, comparing two fields was causing problem. Actually this case is not expected but test data was corrupted. Even so, I added another condition before existing condition as "if data exists" and problem is gone.