C#应用从SQL Server取数遇SqlNullValueException，求空值处理方案

开发C#应用从SQL Server数据库取数，数据库存在空值，希望在应用中替换为默认值，但遇到以下异常：

表结构 #

表包含以下列：

• DeviceSerial（主键，varchar(50)）
• DeviceName（varchar(100)）
• UserName（varchar(100)）
• UserEmail（varchar(100)）
• OverallStatus（varchar(50)）
• 多个可空布尔类型列：MigrationNotStarted、MigrationInProgress、MigrationCompleted、MigrationFailed、IntuneUnenrollment、AzureUnregister、OutlookProfileRemoval、OneDriveUnlink、UserConfirmation、UserReRun、SleepSetting、M365SignOut、TeamsSignOut
• ForesnsitTool（varchar(100)）
• ComputerMigration（可空布尔）
• LastUpdatedTime（可空datetime）
• WaveNumber（varchar）

HomeController代码 #

using Device_Migration_Admin_Portal.Models;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Device_Migration_Admin_Portal.Controllers
{
    [Authorize]
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;
        private readonly DeviceDbContext _context;

        public HomeController(ILogger<HomeController> logger, DeviceDbContext context)
        {
            _context = context;
            _logger = logger;
        }

        // Existing Index action
        public async Task<IActionResult> Index()
        {
            var devices = await _context.DeviceMigrationStatus.ToListAsync();
            var sanitizedDevices = devices.Select(d => new DeviceMigrationStatus
            {
                DeviceSerial = d.DeviceSerial ?? "-",
                DeviceName = d.DeviceName ?? "-",
                UserName = d.UserName ?? "-",
                UserEmail = d.UserEmail ?? "-",
                OverallStatus = d.OverallStatus ?? "-",
                MigrationNotStarted = d.MigrationNotStarted.GetValueOrDefault(),
                MigrationInProgress = d.MigrationInProgress.GetValueOrDefault(),
                MigrationCompleted = d.MigrationCompleted.GetValueOrDefault(),
                MigrationFailed = d.MigrationFailed.GetValueOrDefault(),
                IntuneUnenrollment = d.IntuneUnenrollment.GetValueOrDefault(),
                AzureUnregister = d.AzureUnregister.GetValueOrDefault(),
                OutlookProfileRemoval = d.OutlookProfileRemoval.GetValueOrDefault(),
                OneDriveUnlink = d.OneDriveUnlink.GetValueOrDefault(),
                UserConfirmation = d.UserConfirmation.GetValueOrDefault(),
                UserReRun = d.UserReRun.GetValueOrDefault(),
                SleepSetting = d.SleepSetting.GetValueOrDefault(),
                M365SignOut = d.M365SignOut.GetValueOrDefault(),
                TeamsSignOut = d.TeamsSignOut.GetValueOrDefault(),
                ForesnsitTool = d.ForesnsitTool ?? "-",
                ComputerMigration = d.ComputerMigration.GetValueOrDefault(),
                LastUpdatedTime = d.LastUpdatedTime ?? DateTime.MinValue,
                WaveNumber = d.WaveNumber ?? "-"
            }).ToList();

            return View(sanitizedDevices);
        }

        // Existing Privacy action
        public IActionResult Privacy()
        {
            return View();
        }

        // Existing Error action
        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

模型类代码 #

using System;
using System.ComponentModel.DataAnnotations;

namespace Device_Migration_Admin_Portal.Models
{
    public class DeviceMigrationStatus
    {
        [Key]
        [Required]
        [StringLength(50)]
        public string DeviceSerial { get; set; }

        [StringLength(100)]
        public string DeviceName { get; set; }

        [StringLength(100)]
        public string UserName { get; set; }

        [StringLength(100)]
        public string UserEmail { get; set; }

        [StringLength(50)]
        public string OverallStatus { get; set; }

        public bool? MigrationNotStarted { get; set; }
        public bool? MigrationInProgress { get; set; }
        public bool? MigrationCompleted { get; set; }
        public bool? MigrationFailed { get; set; }
        public bool? IntuneUnenrollment { get; set; }
        public bool? AzureUnregister { get; set; }
        public bool? OutlookProfileRemoval { get; set; }
        public bool? OneDriveUnlink { get; set; }
        public bool? UserConfirmation { get; set; }
        public bool? UserReRun { get; set; }
        public bool? SleepSetting { get; set; }
        public bool? M365SignOut { get; set; }
        public bool? TeamsSignOut { get; set; }

        [StringLength(100)]
        public string ForesnsitTool { get; set; }

        public bool? ComputerMigration { get; set; }
        public DateTime? LastUpdatedTime { get; set; }
        public string WaveNumber { get; set; }
    }
}

错误信息 #

处理请求时发生未处理的异常。
SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
Microsoft.Data.SqlClient.SqlBuffer.get_String()
SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
Microsoft.Data.SqlClient.SqlBuffer.get_String()
lambda_method10(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable+AsyncEnumerator.MoveNextAsync()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken)
Device_Migration_Admin_Portal.Controllers.HomeController.Index() in HomeController.cs
var devices = await _context.DeviceMigrationStatus.ToListAsync();

解决方案 #

1. 定位问题根源 #

异常发生在EF Core读取数据阶段，原因是模型中部分字符串列被定义为非可空类型（string），但数据库对应列存在NULL值。比如DeviceSerial标记了[Required]但数据库中该列实际有NULL，EF Core尝试将NULL转换为非可空string时抛出异常。

2. 分步修复 #

（1）修正模型可空性

将数据库中允许为空的字符串列，在模型中改为可空类型（string?）：

using System;
using System.ComponentModel.DataAnnotations;

namespace Device_Migration_Admin_Portal.Models
{
    public class DeviceMigrationStatus
    {
        [Key]
        [StringLength(50)]
        public string? DeviceSerial { get; set; } // 改为可空，或确保数据库中该列无NULL

        [StringLength(100)]
        public string? DeviceName { get; set; }

        [StringLength(100)]
        public string? UserName { get; set; }

        [StringLength(100)]
        public string? UserEmail { get; set; }

        [StringLength(50)]
        public string? OverallStatus { get; set; }

        // 布尔类型保持可空（bool?）无需修改
        public bool? MigrationNotStarted { get; set; }
        public bool? MigrationInProgress { get; set; }
        public bool? MigrationCompleted { get; set; }
        public bool? MigrationFailed { get; set; }
        public bool? IntuneUnenrollment { get; set; }
        public bool? AzureUnregister { get; set; }
        public bool? OutlookProfileRemoval { get; set; }
        public bool? OneDriveUnlink { get; set; }
        public bool? UserConfirmation { get; set; }
        public bool? UserReRun { get; set; }
        public bool? SleepSetting { get; set; }
        public bool? M365SignOut { get; set; }
        public bool? TeamsSignOut { get; set; }

        [StringLength(100)]
        public string? ForesnsitTool { get; set; }

        public bool? ComputerMigration { get; set; }
        public DateTime? LastUpdatedTime { get; set; }
        public string? WaveNumber { get; set; }
    }
}

（2）提前在EF查询中处理空值

避免先加载所有数据到内存再处理，直接在EF Core查询中转换空值，既高效又能避免读取空值的异常：

public async Task<IActionResult> Index()
{
    var sanitizedDevices = await _context.DeviceMigrationStatus
        .Select(d => new DeviceMigrationStatus
        {
            DeviceSerial = d.DeviceSerial ?? "-",
            DeviceName = d.DeviceName ?? "-",
            UserName = d.UserName ?? "-",
            UserEmail = d.UserEmail ?? "-",
            OverallStatus = d.OverallStatus ?? "-",
            MigrationNotStarted = d.MigrationNotStarted.GetValueOrDefault(),
            MigrationInProgress = d.MigrationInProgress.GetValueOrDefault(),
            MigrationCompleted = d.MigrationCompleted.GetValueOrDefault(),
            MigrationFailed = d.MigrationFailed.GetValueOrDefault(),
            IntuneUnenrollment = d.IntuneUnenrollment.GetValueOrDefault(),
            AzureUnregister = d.AzureUnregister.GetValueOrDefault(),
            OutlookProfileRemoval = d.OutlookProfileRemoval.GetValueOrDefault(),
            OneDriveUnlink = d.OneDriveUnlink.GetValueOrDefault(),
            UserConfirmation = d.UserConfirmation.GetValueOrDefault(),
            UserReRun = d.UserReRun.GetValueOrDefault(),
            SleepSetting = d.SleepSetting.GetValueOrDefault(),
            M365SignOut = d.M365SignOut.GetValueOrDefault(),
            TeamsSignOut = d.TeamsSignOut.GetValueOrDefault(),
            ForesnsitTool = d.ForesnsitTool ?? "-",
            ComputerMigration = d.ComputerMigration.GetValueOrDefault(),
            LastUpdatedTime = d.LastUpdatedTime ?? DateTime.MinValue,
            WaveNumber = d.WaveNumber ?? "-"
        })
        .ToListAsync();

    return View(sanitizedDevices);
}

（3）确保数据库与模型一致性

• 检查主键列DeviceSerial是否真的应该非空：如果是主键，建议清理数据库中的NULL值，同时将模型改回string并保留[Required]；
• 在DbContext的OnModelCreating中配置列规则，确保EF Core与数据库定义匹配：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<DeviceMigrationStatus>()
        .Property(d => d.DeviceSerial)
        .IsRequired(true); // 确保数据库该列非空时配置

    // 其他列可按需配置默认值或可空性
    modelBuilder.Entity<DeviceMigrationStatus>()
        .Property(d => d.DeviceName)
        .HasDefaultValue("-");
}

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