Data is Null. This method or property cannot be called on Null values

Question

I'm working on an application where one can get information on movies from a database as well as add, update and delete the movies. In the database I have three tables (Movie, Genre and MovieGenre <- stores the movies and their genre/s). Everything works fine besides one thing, and that's when a movie hasn't got any genres (which should be possible).

The problem occur in the method below, and the following exception is thrown: Data is Null. This method or property cannot be called on Null values.

The reason (of course) is that the sproc returns null because the movie hasn't got any genres, but I just can't figure out how to prevent this exception being thrown. As I said, it should be possible to store a movie without storing any information of genre/s.

Thanks in advance!

The method:

public List<MovieGenre> GetMovieGenrebyMovieID(int movieID) {

    using (SqlConnection conn = CreateConnection()) {
        try {

            SqlCommand cmd = new SqlCommand("dbo.usp_GetMovieGenreByMovieID", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@MovieID", movieID);

            List<MovieGenre> movieGenre = new List<MovieGenre>(10);

            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader()) {

                int movieGenreIDIndex = reader.GetOrdinal("MovieGenreID");
                int movieIDIndex = reader.GetOrdinal("MovieID");
                int genreIDIndex = reader.GetOrdinal("GenreID");

                while (reader.Read()) {

                    movieGenre.Add(new MovieGenre {
                        MovieID = reader.GetInt32(movieIDIndex),
                        MovieGenreID = reader.GetInt32(movieGenreIDIndex),
                        GenreID = reader.GetInt32(genreIDIndex)
                    });
                }
            }

            movieGenre.TrimExcess();

            return movieGenre;
        }
        catch {
            throw new ApplicationException();
        }
    }
}

The sproc:

ALTER PROCEDURE usp_GetMovieGenreByMovieID
@MovieID int
AS
BEGIN
    BEGIN TRY
        SELECT m.MovieID, g.GenreID, mg.MovieGenreID, g.Genre
        FROM Movie AS m
        LEFT JOIN MovieGenre AS mg
            ON m.MovieId = mg.MovieID
        LEFT JOIN Genre AS g
            ON mg.GenreID = g.GenreID
        WHERE m.MovieID = @MovieID
    END TRY
    BEGIN CATCH
        RAISERROR ('Error while trying to receive genre(s).',16,1)
    END CATCH
END

Answer 1

You shouldn't be trying to convert the null values from the proc into ints - so before you create the MovieGenre instance you need to check the nullable fields using the SqlDataReader.IsDBNull method:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.isdbnull.aspx

Assuming that the GenreID and MovieGenreID are nullable ints you could do something like:

movieGenre.Add(new MovieGenre {
  MovieID = reader.GetInt32(movieIDIndex),
  MovieGenreID = reader.IsDBNull(movieGenreIDIndex) ? null : reader.GetInt32(movieGenreIDIndex),
  GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
});

Answer 2

This error happens immediately after I enabled C# 8 nullable feature in my Entity Framework Core 3.1 project.

The solution is to change your entity properties to their nullable counterparts. For example,

Change from:

public class Person {
  public int Id { get; set; }
  public string Name { get;set; }
  public string Address { get;set; }
}

To:

public class Person {
  public int Id { get; set; }
  public string Name { get;set; }
  public string? Address { get;set; }  //change address to nullable string since it is nullable in database
}

Answer 3

Edit your select statement as follows to handle null issue.

SELECT ISNULL(m.MovieID,0) AS MovieID, 
       ISNULL(g.GenreID,0) AS GenreID, 
       ISNULL(mg.MovieGenreID,0) AS MovieGenreID,
       ISNULL(g.Genre,'') AS Genre
FROM --rest of your query...

Answer 4

In my case I was using EF Core and the issue was that the field was nullable in the database but in the ModelCreating it was required like that:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<MyEntity>(entity =>
   {
      entity.Property(e => e.Details)
                    .IsRequired()
                    .HasMaxLength(250);
   }
}

I remove the IsRequired() and it worked fine.

Update few days after, Got same issue, a string field It was not allowing null in the DB.

Answer 5

The simplest answer is to replace the nulls with non-null values. Try:

ALTER PROCEDURE usp_GetMovieGenreByMovieID
@MovieID int
AS
BEGIN
    BEGIN TRY
        SELECT m.MovieID, 
               coalesce(g.GenreID,0) GenreID, 
               coalesce(mg.MovieGenreID,0) MovieGenreID, 
               coalesce(g.Genre, 'Not Applicable') Genre
        FROM Movie AS m
        LEFT JOIN MovieGenre AS mg
            ON m.MovieId = mg.MovieID
        LEFT JOIN Genre AS g
            ON mg.GenreID = g.GenreID
        WHERE m.MovieID = @MovieID
    END TRY
    BEGIN CATCH
        RAISERROR ('Error while trying to receive genre(s).',16,1)
    END CATCH
END

Answer 6

BookingQuantity - column having null value in DB. but actual DB BookingQuantity not null column. Some rare case it happens to enter. In that case below code throw error the same error(Data is Null. This method or property cannot be called on Null values ).

totalBookingQuantity += item.InspPoTransactions.Where(x => x.PoId == inspectionPODetail.PoId && x.ProductId == inspectionPODetail.ProductId).Sum(x => Convert.ToInt32(x.BookingQuantity));

Answer 7

Today I've faced this issue. But mine has been fixed in another way. If someday anyone stumbled the answer is for them.

As this is a generic C# .NET CLI exception

I've added a new foreign key to one of my DB table with no default value. Thus the value was set to NULLas that column was set to allow null. And I've been getting this exception while querying on that table.

As solution, I replaced the NULL values with appropriate values (as they are foreign key' they should be appropriate).

That's all.

Thank you.

Answer 8

For me this happened because in the database I had a column 'XYZ' which had a NULL value, but the model's property that mapped to it (bool) wasn't nullable.

Answer 9

I had this problem in .net5 data first project , because a field in data base was nullable but in c# entity class was required. after recreate entities by ef core power tools extension , the problem resolved.