EF Core: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values

Based on our previous discussion , if you still face challenges with the recent changes in EF Core nullability rules, particularly concerning string properties inside NRT (Nullable Reference Types) enabled projects. These changes can lead to unexpected behaviors, especially when handling nullable data from the database.

One crucial point to address is the mechanism for detecting which column or property is causing the issue. If we attempt to read nullable data from the database but our entity type does not allow nullability, we might encounter the error message: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

For instance, if 'Quantity' is nullable in the database and our entity is defined as follows:

public class Product
{
    public int Id { get; set; }
    public int Quantity{ get; set; }
}
  

Yo'll encounter the mentioned exception. To resolve this issue, you need to adjust the type of the 'Quantity' property to allow for nullability:

public class MyEntity
{
    public int Id { get; set; }
    public int? Quantity{ get; set; }
}
  

This change specifies that 'Quantity' can accept null values, aligning with the nullable data in the database. By updating you entity type accordingly, you ensure compatibility and avoid errors related to nullability mismatches.

Understanding how nullability rules in EF Core interact with database data is importent for maintaining data integrity and preventing runtime errors. By explicitly defining nullable properties in our entity types, we ensure consistency between our application code and the underlying database schema, enhancing robustness and reliability.

As we upgrade to Entity Framework Core 6.0.28, we encounter an error when reading from our database. Strangely, all of this code was working perfectly before the upgrade.

We realize that this exception can also occur if we enable the latest Nullable feature from C# 8. Unfortunately, EF Core isn't fully compatible with C# 8 nullable types at the moment.

For instance, even if a property in our entity class is not marked with the [Required] attribute, EF Core raises exceptions because it expects the value in the database to not be null. This behavior is due to a change introduced in C# 8.0.

The solution to this issue is to disable the Nullable feature on the project level. We can achieve this by adding the following code to our .csproj file:

<PropertyGroup>
  <Nullable>disable</Nullable>
</PropertyGroup>
  

Disabling the Nullable feature on the project level provides a workaround for the compatibility issue between EF Core and C# 8 nullable types. 

In our scenario, if we want to ignore null values and ensure that our queries return an empty collection instead, we can use the DefaultIfEmpty() method.

Here's how we implemented this solution:

var products = await _context.Products
    .OrderBy(p => p.ProductId == 1 
        ? "_" + p.ProductName 
        : p.ProductName
    )
    .Select(a => new ProductEdit(..--..))
    .DefaultIfEmpty()
    .ToListAsync();
  

By adding the DefaultIfEmpty() method to our query, we ensure that even if the result set is empty or contains null values, our query will return an empty collection instead of throwing exceptions.

To resolve a similar issue with the "Data is Null" exception, we found that we needed to explicitly specify IsRequired(false) on our column mapping. In our case, we were mapping a database view.

builder.Property(x => x.About).IsRequired(false);
  

By explicitly specifying IsRequired(false) on our column mapping, we inform EF Core that the corresponding column in the database allows null values. This ensures that EF Core does not raise exceptions related to null values when reading from the database.