Entity Framework转换模型时遇System.DBNull转String异常

问题背景 #

用Entity Framework做服务端分页，第一步统计总记录数正常返回24k条，但将查询结果转换为ContactListModel时抛出异常：Unable to cast object of type 'System.DBNull' to type 'System.String'。模型中所有字符串属性都设为可空，仍无法解决，需要定位具体是哪个属性导致的问题。

获取总记录数代码 #

// 统计总记录数
int totalResultsCount = await query.CountAsync().ConfigureAwait(false);

转换模型的查询代码 #

var dataset = await query.Select(c => new ContactListModel
{
    Id = c.Id,
    FirstName = c.FirstName,
    LastName = c.LastName,
    Email = c.Email,
    EmailAlternative = c.EmailAlternative,
    JobTitle = c.JobTitle,
    CompanyName = (c.Company != null) ? c.Company.Name : "",
    StatusValue = c.Status,
    Modified = c.Modified,
    AddressCountry =   (c.Addresses.FirstOrDefault(a => a.IsPrimary) != null) ?
                       (c.Addresses.FirstOrDefault(a => a.IsPrimary).Country!=null) ? c.Addresses.FirstOrDefault(a => a.IsPrimary).Country.Name : "" 
                      :""                    
}).OrderByDynamic(sortBy, sortDirection).Paginate((tableState.Page * tableState.PageSize), tableState.PageSize).ToListAsync();

异常信息 #

Unable to cast object of type 'System.DBNull' to type 'System.String'

ContactListModel定义 #

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

    public string Title { get; set; }

    public string FirstName { get; set; }
     
    public string LastName { get; set; }

    /// <summary>
    /// 将名、姓和中间名合并为一个空格分隔的字符串
    /// </summary> 
    public string Name
    {
        get
        {
            return string.Join(" ",
                          new[] { Title, FirstName, LastName }
                          .Where(s => !string.IsNullOrWhiteSpace(s))
                          );
        }
    }


    public string Email { get; set; }

    public string EmailAlternative { get; set; }

    public string JobTitle { get; set; }

    public string CompanyName { get; set; }

    
    //public ContactAddress Address { get; set; }

    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string AddressLine3 { get; set; }
    public string AddressLine4 { get; set; }

    public string AddressCountry { get; set; }

    public string AddressCounty { get; set; }
    public string AddressCountyText { get; set; } 
    public string AddressPostCode { get; set; }
    public string AddressTown { get; set; }
     
    public bool Modified { get; set; } = false;

    public bool Checked { get; set; } = false;

    public ContactStatus  StatusValue { get; set; }

    /// <summary>
    /// 将StatusValue转换为前端显示的可读状态
    /// </summary>
    public ContactStatus Status
    {
        get
        {
            if (StatusValue == ContactStatus.Verified)
                return ContactStatus.Active;
            else
                return StatusValue;
        }
    } 
}

排查与解决方法 #

1. 逐步排查定位问题属性 #

最直接的方式是简化Select语句，先只保留Id属性，执行查询确认正常后，逐个添加其他属性，每次执行后观察是否触发异常。一旦添加某个属性后出现错误，该属性就是问题根源。

示例简化代码：

.Select(c => new ContactListModel { Id = c.Id })

2. 优化空值处理逻辑 #

数据库中的DBNull和C#的null不是同一类型，即使模型属性是可空string，直接赋值DBNull仍会报错。可以用空合并运算符??简化空值判断，确保将DBNull转换为null或空字符串：

var dataset = await query.Select(c => new ContactListModel
{
    Id = c.Id,
    FirstName = c.FirstName ?? "",
    LastName = c.LastName ?? "",
    Email = c.Email ?? "",
    EmailAlternative = c.EmailAlternative ?? "",
    JobTitle = c.JobTitle ?? "",
    CompanyName = c.Company?.Name ?? "",
    StatusValue = c.Status,
    Modified = c.Modified,
    AddressCountry = c.Addresses.FirstOrDefault(a => a.IsPrimary)?.Country?.Name ?? ""
})
.OrderByDynamic(sortBy, sortDirection)
.Paginate((tableState.Page * tableState.PageSize), tableState.PageSize)
.ToListAsync();

3. 查看EF生成的SQL并验证数据库数据 #

启用EF的查询日志，获取生成的SQL语句，直接在数据库中执行该SQL，查看返回结果中哪个字段是DBNull，对应到模型的属性即可定位问题。

EF Core启用日志示例：

public class YourDbContext : DbContext
{
    private readonly ILogger<YourDbContext> _logger;

    public YourDbContext(DbContextOptions<YourDbContext> options, ILogger<YourDbContext> logger)
        : base(options)
    {
        _logger = logger;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(message => _logger.LogInformation(message), LogLevel.Information);
    }
}

4. 检查数据库字段与模型的映射 #

确认数据库中对应字符串属性的字段类型是否与模型匹配，比如是否存在字段是char/nchar类型但长度不足，或者字段允许为NULL但EF的映射配置有问题（比如错误设置了Required）。

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