Deep Analysis and Solutions for SqlNullValueException in Entity Framework Core

Exception Phenomenon and Background Analysis

After upgrading Entity Framework Core from version 2.0.2 to 2.2.0 or higher, developers frequently encounter the SqlNullValueException: Data is Null. This method or property cannot be called on Null values exception. This indicates that EF Core encountered a field value that was expected to be non-null but was actually null when reading database records.

Root Cause Investigation

The core issue lies in the mapping inconsistency between entity models and database table structures. Specifically: string properties in entity classes are marked with the [Required] attribute, indicating these properties should have NOT NULL constraints in the database, but the actual database tables allow NULL values for the corresponding varchar columns.

Taking the Company entity as an example, the following properties are marked as required in the model:

[Required]
public string CompanyStreetAddress { get; set; }
[Required]
public string CompanyCity { get; set; }
[Required]
public string CompanyZipCode { get; set; }

However, in the database creation script, the corresponding column definitions allow null values:

CompanyStreetAddress varchar(256),
CompanyCity varchar(256),
CompanyZipCode varchar(10),

Impact of EF Core Version Differences

In EF Core 2.0.2, such mapping inconsistencies might be ignored, but in version 2.2.0 and above, EF Core enforces stricter data integrity checks. When EF Core executes queries and attempts to read NULL values from the database into string properties marked as required, it throws the SqlNullValueException.

Problem Diagnosis Methods

Enabling Detailed Error Logging

Enable detailed error information in DbContext configuration:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.EnableDetailedErrors();
    // Other configurations...
}

After enabling, exception messages become more specific, for example: An error occurred while reading a database value for property 'Company.CompanyStreetAddress'. The expected type was 'System.String' but the actual value was null.

Database Data Inspection

Execute SQL queries to check columns that might contain NULL values:

SELECT CompanyId, CompanyStreetAddress, CompanyCity, CompanyZipCode 
FROM T_Companies 
WHERE CompanyStreetAddress IS NULL 
   OR CompanyCity IS NULL 
   OR CompanyZipCode IS NULL;

Solution Approaches

Solution 1: Correct Entity Model

If database columns genuinely allow NULL values, remove the [Required] attribute from entity properties:

// Corrected property definitions
public string CompanyStreetAddress { get; set; }
public string CompanyCity { get; set; }
public string CompanyZipCode { get; set; }

Solution 2: Fix Database Constraints

If business logic requires these columns to be non-null, modify the database table structure:

ALTER TABLE T_Companies 
ALTER COLUMN CompanyStreetAddress varchar(256) NOT NULL;

ALTER TABLE T_Companies
ALTER COLUMN CompanyCity varchar(256) NOT NULL;

ALTER TABLE T_Companies 
ALTER COLUMN CompanyZipCode varchar(10) NOT NULL;

Solution 3: Use Nullable Reference Types

In C# 8.0 and above, enable nullable reference types to express intentions more clearly:

#nullable enable

public class Company
{
    public string? CompanyStreetAddress { get; set; }
    public string? CompanyCity { get; set; }
    public string? CompanyZipCode { get; set; }
    // Other properties...
}

Preventive Measures

To avoid similar issues, it's recommended to:

• Ensure consistency between entity models and database structures from project inception
• Use EF Core migration features to manage database schema changes
• Enable detailed error logging in development environments for quick problem identification
• Conduct regular data integrity checks

Conclusion

The root cause of SqlNullValueException is mapping inconsistency between entity models and database structures. By enabling detailed error logging, inspecting database data, and correcting mapping relationships, this issue can be effectively resolved. When upgrading EF Core versions, special attention should be paid to such potential data integrity problems to ensure stable application operation.