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

Question

I cannot for the life of me figure out what the issue is. Every time the query goes to execute "ToList()", I get the error above.

Here's more information on it:

<Error>
<Message>An error has occurred.</Message>
<ExceptionMessage>
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.
</ExceptionMessage>
<ExceptionType>System.InvalidOperationException</ExceptionType>
<StackTrace>
at System.Data.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal) at System.Data.Common.Internal.Materialization.Shaper.GetColumnValueWithErrorHandling[TColumn](Int32 ordinal) at lambda_method(Closure , Shaper ) at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper) at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext() at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at TVDataWebAPI.Controllers.ETSShowsController.GetETSShows(String title, String episodeTitle, String genre, String showTypeDescription, String directorName, String releaseYear, String seasonEpisode) in c:\Users\rarbex\Documents\Visual Studio 2012\Projects\TVDataWebAPI\TVDataWebAPI\Controllers\ETSShowsController.cs:line 83 at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.<>c__DisplayClass5.<ExecuteAsync>b__4() at System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken)
</StackTrace>
</Error>   

public IEnumerable<ETSShows> GetETSShows(string title = null,
{
   string episodeTitle = null, string genre = null,
   string showTypeDescription = null,
   string directorName = null,
   string releaseYear = null,
   string seasonEpisode = null)
   {
      IQueryable<ETSShows> query = from shows in db.ETS_Shows
      from episodes in db.ETS_Episodes.Where(v => v.ShowId == shows.ShowId).DefaultIfEmpty()
      from genres in db.ETS_LKP_Genres.Where(s => s.GenreCode == shows.GenreCode).DefaultIfEmpty()
      from showTypes in db.ETS_LKP_ShowTypes.Where(z => z.ShowTypeCode == shows.ShowTypeCode).DefaultIfEmpty()
      from directors in db.ETS_Directors.Where(p => p.ShowId == shows.ShowId).DefaultIfEmpty()
      select new ETSShows
      {
         dataSource = "ETS",
         Title = shows.Title,
         EpisodeId = episodes.EpisodeId,
         EpisodeTitle = episodes.EpisodeTitle,
         GenreDescription = genres.GenreDescription,
         ShowTypeDescription = showTypes.ShowTypeDescription,
         Name = directors.Name,
         ReleaseYear = (int) shows.ReleaseYear,
         SeasonEpisode = episodes.SeasonEpisode,
         ShowId = shows.ShowId
      };
   }
}

public class ETSShows
{
   public string dataSource { get; set; }
   public string Title { get; set; }
   public int EpisodeId { get; set; }
   public string EpisodeTitle { get; set; }
   public string GenreDescription { get; set; }
   public string ShowTypeDescription { get; set; }
   public string Name { get; set; }
   public int ReleaseYear { get; set; }
   public string SeasonEpisode { get; set; }
   public int ShowId { get; set; }
}

possible duplicate of [The cast to value type 'Int32' failed because the materialized value is null](http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) — Rawling, Feb 05 '14 at 08:35

score 9 · Accepted Answer · answered Jun 21 '13 at 17:25

I'm guessing the issue is right here:

ReleaseYear = (int) shows.ReleaseYear

Why do you have to cast shows.ReleaseYear to an int? Is it because it's not already an int? Maybe it's actually a Nullable<int>?

An int can't hold a null value, and the error is telling you that it's encountered a null value in the data, so that value can't be cast to an int.

You'll either need to change your data to not allow null values for that field, or change your type to a Nullable<int> (or int? for short).

Thanks. All I did was to add "?" to the Shows class to this variable. — JJ., Jun 21 '13 at 17:47

score 7 · Answer 2 · answered Jun 21 '13 at 17:24

It tells you right in there:

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

This is the offending line:

ReleaseYear = (int) shows.ReleaseYear,

Check your data and make sure everything has a ReleaseYear, or switch to using int?.

score 3 · Answer 3 · answered Jun 21 '13 at 17:31

If you are getting null values then try using these methods for casting -

Convert.ToInt32(string), This would return 0 if value used for parsing is null.

Int32.TryParse(string, out int), this would return true / false when it could parse / not respectively.

In your program before consuming the parsed value, check whether it is valid or not.

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

3 Answers3