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.

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

Dour

This: catch { throw new ApplicationException(); } is a pretty bad idea. You are losing ALL context information. At least pass the caught exception on as inner exception: catch(Exception ex){ throw new ApplicationException(ex); } – Pasahow

Does this answer your question? Entity Framework Core: `SqlNullValueException: Data is Null.` How to troubleshoot? – Jacinda

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

Clavicembalo

you might need to add the code as reader.IsDBNull(movieGenreIDIndex) ? (int?)null : reader.GetInt32(movieGenreIDIndex) – Waves

How do you find out which property the error occurred on? – Vauban

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
}

Wellread

string is nullable. – Mertens

@Jocie I was referring to the new C# 8 nullable reference type. learn.microsoft.com/en-us/dotnet/csharp/nullable-references, here string can be string or string? I have bolded that wording in my answer to make it clearer. – Wellread

That's interesting, I wonder why you would need to do that when string is already nullable. Good find though. – Mertens

@Vitani, it seems to be more a problem of bad verbiage. String is not already nullable (i.e. Nullable<string>), it is simply a class, which is a reference type and not a struct which is a value type. All of sudden we are required supposed to explicitly put a question mark after all of these reference types. I'm still a bit foggy on exactly why as well.. – Learned

I started getting this when upgrading a project from .NET Core 3 to .NET 6. Any string that can be returned as NULL from SQL Server needs to be defined as a nullable string. – Nidianidicolous

Answer 3

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.

Phidias

Also, if you don't have .IsRequired() in model Builder you can check [Required] annotation in your entity – Brow

This was my problem too, in the database a have a field that allows null but in my EF Configuration I had set it was requiered. Thanks so much. – Probabilism

Answer 4

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

Annulation

Thanks for answering. I tried your code but unfortunately I get the same error :/ – Dour

I used ISNULL only for GenreID and Genre columns to show you. You can use it for every column as ANSWER UPDATED TO INCLUDE ALL COLUMNS – Annulation

If you're on MySQL you have to use IFNULL. – Gullible

Answer 5

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

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

For columns that are nullable in your DB tables:

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.

Temporize

I had this issue too, but in my case removing the [Required] attribute was enough to make the error go away. – Decompensation

@Decompensation , that's interesting. Others on this page have had similar luck. I tried removing the IsRequired attribute, but didn't have any luck. Example core property in dbcontext object: entity.Property(e => e.VoProp).IsRequired(false).HasColumnName("Col"); Where entity is a ModelBuilder Entity. If one is mapping an existing table, I'm not sure why it would code a nullable field as required. What I had to do is go into the Model (value object) and add "?" to the types. Again, not sure why it wouldn't have done that knowing the column's metadata and whether nullable. – Temporize

(Sorry if you're getting a notifcation for all these edits... I end up fighting this thing so much.) I just wanted to say again that I am using EF Core. Are you using EF6 (or earlier)? I wonder if the difference between our experiences is due to the version. With core, it didn't add attribute tags to any of my model properties. None that I've seen, anyway. – Temporize

I am on EF Core 6, last I had the problem was specifically 6.0.3. I do get similar errors when trying to project the database models into a dto and I have null members, e.g. .Select(p => new Model { NotNullableObject = p.NullableNaviationProperty.NullValue } ) I keep coming across the problem on different models and promptly forget all about this and have to google the problem and find my own reply here ha! – Decompensation

Regarding why a nullable can be required, I think that you can technically mark it as Required and the attribute will validate all incoming models to make it required, but you will only get an error selecting WHEN the data null, i.e. you can Context.DbSet.Where(p => p.Property != null) and you will not get an error. I guess the use case is you come across a nullable field you want required, update all data to be not null, but don't update the schema to not null yet – Decompensation

Answer 6

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

Klepac

1+ for coalesce ISNULL don't work for me i use MySql – Celibate

MySQL uses iFnull not iSnull. – Temporize

Answer 7

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.

Natatorial

This happens with us, as we added a field in development db and updated default value to false. We added constraint for the same. But we missed to update existing data on production server. – Lait

Answer 8

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

I am building WEB Api. Before I put in place [Required] Attribue - https://learn.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.

Runge

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Winding

Answer 9

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.

Backsaw

Answer 10

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.

Rainproof

Answer 11

I had this problem too, and for me a property in my class was decorated as [Required] but the table had null values in that column. Makes sense. Once I removed Required, the data loaded successfully with null values.

Aquileia

Answer 12

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

Danieldaniela

Answer 13

I got the same issue and found out that I had made the LastName attribute in Person table nullable in the database but forgot to remove the Required annotation on the model class.

public class Person {
  public int Id { get; set; }

  [Required]
  public string LastName { get; set; }

}

Removing the required attribute fixed the error. Hope this helps someone

Thermotherapy

Answer 14

I don't know if someone gave the answer for it. In my case, i've undestand that the method or property cannot be called on Null values, so i insert a "?" for all of my entity properties and it worked. Just set all properties as "nullable", like this:

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
}

Leacock

Your answer correctly shows how to declare both value-type and reference-type properties as nullable, but that isn't the problem in the question. The problem in the question is that the querent is calling SqlDataReader.GetInt32(movieGenreIDIndex) and it is throwing a Data is Null exception; they want to prevent that. Changing the declaration of the properties won't suppress the exception from the reader. There is an already-accepted answer to check SqlDataReader.IsDBNull(movieGenreIDIndex). – Keesee

Now if the querent had the related problem that they were pulling data from the database using Entity Framework Core rather than via a SqlDataReader and experienced the exception shown, then declaring the properties as nullable would indeed likely be helpful, however this answer and this answer also already cover that related problem. – Keesee

So, might you please edit your answer to clarify how it applies to the question? Or if it does not apply, might you please go ahead and delete it? Thanks! from review. – Keesee

Answer 15

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

Rathe