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

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

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

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
}

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...

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.

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

If somebody have faced this issue, here is my case.

I am building WEB Api. Before I put in place [Required] Attribue - https://docs.microsoft.com/en-us/dotnet/api/system.componentmodel.dataannotations.requiredattribute?view=net-5.0, I had some NULL values in my database. Then I added this attribute to my model and was trying to make a GET request, but "System.InvalidOperationException: The data is NULL at ordinal 1. This method can't be called on NULL values. Check using IsDBNull before calling." appeared.

So I deleted NULL values from databases and every request worked fine after that. As far as I understood, an error occurs because of EF Core doesn't allow NULL values in database while [Required] attribute applied.

I hope it will be helpful for someone.

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.

I realize this is old, but I just had this problem for EF Core in .net 6.

Even though strings are nullable, and Entity Framework even created my objects from the existing table with the string type, I had to change the type of my string columns to the string? type in order to pull back data where the data was null.

wrong:

public string Decision { get; set; }

correct:

public string? Decision { get; set; }

I thought I was having an issue with dependency injection. Turns out it was Entity Framework Core and it was a simple fix.

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.

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.

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

This error might occur due to lack of permissions of the user on the database. Check if the user has at least the EXECUTE, SELECT or SHOW DATABASES permissions