.NET Core API中SqlNullValueException异常排查与解决

安全阿岳

2026-6-25

解决SqlNullValueException：读取Course数据时遇到NULL值问题

我需要获取TrendingCourseDto类的所有字段，以下是实现代码：

public async Task<IEnumerable<TrendingCourseDto>> GetTrendingCoursesAsync()
{
   var trendingCourses = await _categoryDbContext.Courses
       .Include(c => c.Instructor)
       .Include(c => c.Reviews)
       .Where(c => c.Status == "Active")
       .OrderByDescending(c => c.Popularity)
       .Take(6)
       .ToListAsync();

   var trendingCourseDtos = trendingCourses.Select(c => new TrendingCourseDto
   {
       InstructorName = c.Instructor?.FirstName + " " + c.Instructor?.LastName,
       CourseTitle = c.Title,
       Popularity = c.Popularity,
       CourseDescription = c.Description,
       ImageURL = c.ImageURL,
       NumberOfLessons = CalculateNumberOfLessons(c),
       Fee = c.Price.HasValue ? c.Price.ToString() : "Free",
       AverageRating = CalculateAverageRating(c.Reviews)
   });

   return trendingCourseDtos;
}

private int CalculateNumberOfLessons(Course course)
{
   if (course.Sections == null)
   {
       return 0;
   }
   else
   {
       return course.Sections.Sum(s => s.Lessons?.Count ?? 0);
   }
}

private double CalculateAverageRating(ICollection<Review> reviews)
{
   if (reviews != null && reviews.Any())
   {
       return reviews.Average(r => r.Rating);
   }
   return 0;
}

相关实体类和DTO类：

public class Course:AuditableEntity
 {
     [Key]
     public int CourseID { get; set; }
     public int InstructorID { get; set; }
     public int CategoryID { get; set; }
     public string? Title { get; set; }
     public string? Description { get; set; }
     public decimal? Price { get; set; }
     public string? Level { get; set; }
     public string? Language { get; set; }
     public int Duration { get; set; }
     public string? Prerequisites { get; set; }
     public string? Status { get; set; }
     public string? ImageURL { get; set; }
     public int Popularity { get; set; }

     public virtual Instructor ?Instructor { get; set; }
     public virtual Category ?Category { get; set; }
     public virtual ICollection<Section>? Sections { get; set; }
     public virtual ICollection<Quiz>? Quizzes { get; set; }
     public virtual ICollection<Assignment>? Assignments { get; set; }
     public virtual ICollection<Review>? Reviews { get; set; }
 }

 public class Category: AuditableEntity
 {
     [Key]
     public int CategoryID { get; set; }
     public string? CategoryName { get; set; } 
     public string? CategoryDescription { get; set; }
     public string? CssIconClass { get; set; }
     public virtual ICollection<Course>? Courses { get; set; }
 }
 public class Lesson:AuditableEntity
 {
     [Key]
     public int LessonID { get; set; }
     public int SectionID { get; set; }
     public string? Title { get; set; }
     public string? Content { get; set; }
     public int Duration { get; set; }
     public int OrderIndex { get; set; }
     public virtual Section? Section { get; set; }
 }
 public class Instructor:AuditableEntity
 {
     [Key]
     public int InstructorID { get; set; }
     public int UserID { get; set; }
     public string ?FirstName { get; set; }
     public string ?LastName { get; set; }
     public string? Bio { get; set; }
     public string? ProfilePicture { get; set; }
     public string? Website { get; set; }
     public string? SocialMediaLinks { get; set; }

     public virtual User? User { get; set; }
     public virtual ICollection<Course>? Courses { get; set; }
 }
public class Review:AuditableEntity
{
    [Key]
    public int ReviewID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
    public int Rating { get; set; }
    public string ?Comment { get; set; }
    public DateTime ReviewDate { get; set; }

    public virtual Course? Course { get; set; }
    public virtual Student? Student { get; set; }
}
 public class Section:AuditableEntity
 {
     [Key]
     public int SectionID { get; set; }
     public int CourseID { get; set; }
     public string ?Title { get; set; }
     public string ?Description { get; set; }
     public int OrderIndex { get; set; }
     public virtual Course ?Course { get; set; }
     public virtual ICollection<Lesson> ?Lessons { get; set; }
 }

public class TrendingCourseDto
{
    public string? InstructorName { get; set; }
    public string? CourseTitle { get; set; }
    public int Popularity { get; set; }

    public string? CourseDescription { get; set; }
    public string? ImageURL { get; set; }
    public int NumberOfLessons { get; set; }
    public string? Fee { get; set; }
    public double AverageRating { get; set; }
}

调试时，执行这段代码失败：

var trendingCourses = await _categoryDbContext.Courses
       .Include(c => c.Instructor)
       .Include(c => c.Reviews)
       .Where(c => c.Status == "Active")
       .OrderByDescending(c => c.Popularity)
       .Take(6)
       .ToListAsync();

报错信息：

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
at Microsoft.Data.SqlClient.SqlBuffer.get_Int32()
at Microsoft.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at lambda_method39(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at lambda_method6(Closure, Object) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

问题原因

这个错误是因为数据库中Course表的某个非可空int类型字段存在NULL值，但实体类中对应的属性是int（非可空值类型），EF Core从数据库读取NULL值时无法转换为int，从而抛出异常。

查看Course实体，以下int类型属性未标记为可空：

public int InstructorID { get; set; }
public int CategoryID { get; set; }
public int Duration { get; set; }
public int Popularity { get; set; }

如果数据库中这些列允许NULL，或已存在NULL数据，就会触发报错。

解决办法

方案1：修复数据库数据和表结构

查询Course表，找出上述int列中存在NULL的记录，将NULL更新为合理默认值（比如Duration设为0，InstructorID关联有效讲师ID）。
修改数据库表结构，将这些列设置为NOT NULL，避免后续插入NULL值。

方案2：修改实体类属性为可空类型

如果业务逻辑允许这些字段为空，将实体类中对应属性改为可空int（int?）：

public class Course:AuditableEntity
{
    // 其他属性不变
    public int? InstructorID { get; set; }
    public int? CategoryID { get; set; }
    public int? Duration { get; set; }
    public int? Popularity { get; set; }
    // 其他属性不变
}

修改后更新数据库迁移（若使用EF迁移），确保表结构与实体类一致。

方案3：查询时过滤NULL值

若暂时不想修改结构，可在查询时过滤掉含NULL值的记录（临时方案，不推荐长期使用）：

var trendingCourses = await _categoryDbContext.Courses
    .Include(c => c.Instructor)
    .Include(c => c.Reviews)
    .Where(c => c.Status == "Active" 
        && c.InstructorID != null 
        && c.CategoryID != null 
        && c.Duration != null 
        && c.Popularity != null)
    .OrderByDescending(c => c.Popularity)
    .Take(6)
    .ToListAsync();

内容的提问来源于stack exchange，提问作者MJ X

.NET Core API中SqlNullValueException异常排查与解决

问题原因 #

解决办法 #

方案1：修复数据库数据和表结构 #

方案2：修改实体类属性为可空类型 #

方案3：查询时过滤NULL值 #

问题原因

解决办法

方案1：修复数据库数据和表结构

方案2：修改实体类属性为可空类型

方案3：查询时过滤NULL值