为何SQL Server认为双层嵌套Owned Entity不为空？问题咨询

• 需求：
  • 定义Foo结果实体，包含可选的Bar详情实体（可能因错误不存在）
  • Bar包含多个内容，其中包括可复用的Baz实体
  • Bar为可选，但当Bar存在时，Baz必须同时存在
  • Baz类型需单独定义以实现复用
• 问题：从SQL Server加载数据时，即使Bar为null（此时Baz也应为null），系统仍报错认定Baz不为空；内存数据库无此问题
• 核心疑问：
  1. 该问题的成因是什么？如何正确配置此关系？
  2. 有哪些可行的解决办法？

异常信息 #

Unhandled exception. System.InvalidOperationException: An error occurred while reading a database value for property 'BazDto.BarDtoFooDtoId'. The expected type was 'System.Guid' but the actual value was null.
 ---> 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_Guid()
   at Microsoft.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)
   at lambda_method32(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   --- End of inner exception stack trace ---
   at lambda_method32(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Program.Main(String[] args) in D:\paul\CODE\playground\Playground_02\ConsoleApp1\Program.cs:line 88
   at Program.Main(String[] args) in D:\paul\CODE\playground\Playground_02\ConsoleApp1\Program.cs:line 88
   at Program.<Main>(String[] args)

可复现示例 #

步骤 #

1. 创建.NET 8控制台应用项目
2. 添加以下包引用到.csproj文件
3. 将下方代码替换Program.cs内容
4. 更新连接字符串指向SQL Server实例
5. 生成数据库迁移文件并执行
6. 运行项目即可复现问题

代码实现 #

Program.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

public class FooDto
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Id { get; set; }

    public BarDto? Bar { get; set; }
}

[Owned]
public class BarDto
{
    public BazDto Baz { get; set; } = null!;

    // 避免EF Core 6.0+警告：嵌套可选从属实体共享表且无必填属性时不被允许
    public int _RequiredFieldToAvoidWarning { get; set; }
}

[Owned]
public class BazDto
{
    public int Id { get; set; }
}

public class ExampleContext : DbContext
{
    public DbSet<FooDto> Foos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        base.OnConfiguring(options);

        options.EnableDetailedErrors();

        options.UseLoggerFactory(LoggerFactory.Create(builder =>
        {
            builder.AddConsole();
            builder.SetMinimumLevel(LogLevel.Trace);
        }));

        var connectionString = @"CONNECTION_STRING"; // 替换为你的SQL Server连接字符串
        options.UseSqlServer(connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<FooDto>(fooBuilder =>
        {
            fooBuilder.OwnsOne(x => x.Bar, barBuilder =>
            {
                barBuilder
                    .OwnsOne(x => x.Baz, bazBuilder =>
                    {
                        // 单独建表才会触发此问题
                        bazBuilder.ToTable("Baz");
                    });
            });
        });
    }
}

internal class Program
{
    public static async Task Main(string[] args)
    {
        await using var context = new ExampleContext();

        await context.Database.MigrateAsync();
        
        context.Foos.Add(new FooDto()
        {
            Id = Guid.NewGuid(),
            Bar = null,
        });
        
        await context.SaveChangesAsync();

        // 此处会抛出异常
        var result = await context.Foos
            .AsNoTracking()
            .Include(x => x.Bar)
            .ThenInclude(x => x!.Baz)
            .FirstAsync();
    }
}

.csproj包引用

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.10" />
<PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="8.0.10" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.10" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.10">
<PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.Extensions.Logging" Version="8.0.1" />
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="8.0.1" />

日志信息 #

日志包含EF Core的详细查询追踪内容，显示查询执行时尝试从Baz表读取关联数据，但由于Bar为null，Baz表中无对应记录，导致外键字段返回null，触发类型转换异常。

成因 #

当将Baz配置为单独表的Owned实体时，EF Core会自动为Baz表生成外键BarDtoFooDtoId（关联Foo的Id），并将该外键默认配置为非nullable的Guid类型。

当Foo的Bar为null时，查询使用左连接关联Baz表，此时Baz表的BarDtoFooDtoId字段返回null，但EF Core尝试将其映射到非nullable的Guid属性，因此抛出SqlNullValueException。内存数据库对null值的处理更宽松，不会触发此异常。

同时，BarDto中Baz被声明为非nullable（public BazDto Baz { get; set; } = null!;），EF Core据此认为Baz是Bar的必填项，但未考虑Bar本身为可选的场景，导致查询逻辑冲突。

正确配置方式 #

需要同时满足两个核心需求：Bar可选，Bar存在时Baz必填。可通过以下方式实现：

解决办法1：配置Baz的外键为可null类型 #

在OnModelCreating中显式将Baz的外键设置为可null的Guid，允许Baz表中存在外键为null的记录（对应Bar为null的场景）：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<FooDto>(fooBuilder =>
    {
        fooBuilder.OwnsOne(x => x.Bar, barBuilder =>
        {
            barBuilder.OwnsOne(x => x.Baz, bazBuilder =>
            {
                bazBuilder.ToTable("Baz");
                // 显式配置外键为可null的Guid
                bazBuilder.Property<Guid?>("BarDtoFooDtoId");
            });
        });
    });
}

解决办法2：调整实体属性并添加约束 #

修改BarDto中Baz的属性为可null，同时通过数据验证或数据库约束确保Bar存在时Baz不为null：

1. 修改实体定义

[Owned]
public class BarDto
{
    public BazDto? Baz { get; set; }

    [Required]
    public int _RequiredFieldToAvoidWarning { get; set; }
}

2. 添加数据库检查约束

在OnModelCreating中添加约束，确保当Bar的必填字段存在时，Baz的外键也必须存在：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<FooDto>(fooBuilder =>
    {
        fooBuilder.OwnsOne(x => x.Bar, barBuilder =>
        {
            barBuilder.OwnsOne(x => x.Baz, bazBuilder =>
            {
                bazBuilder.ToTable("Baz");
            });
            // 添加检查约束：当Bar存在时，Baz必须存在
            barBuilder.HasCheckConstraint("CK_Bar_Baz_Required", 
                "[_RequiredFieldToAvoidWarning] IS NOT NULL AND [BarDtoFooDtoId] IS NOT NULL");
        });
    });
}

3. 可选：添加业务层验证

在保存数据前手动验证逻辑，确保Bar存在时Baz不为null：

// 在ExampleContext中重写SaveChangesAsync
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    foreach (var entry in ChangeTracker.Entries<FooDto>())
    {
        if (entry.Entity.Bar != null && entry.Entity.Bar.Baz == null)
        {
            throw new InvalidOperationException("Bar存在时，Baz不能为空");
        }
    }
    return await base.SaveChangesAsync(cancellationToken);
}

解决办法3：避免Baz单独建表（仅适用于无需独立表的场景） #

如果不需要Baz单独存储，可将其与Bar一起嵌入Foo的表中，此时EF Core会自动处理null逻辑，不会触发异常：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<FooDto>(fooBuilder =>
    {
        fooBuilder.OwnsOne(x => x.Bar, barBuilder =>
        {
            // 移除ToTable配置，Baz将嵌入Foo表中
            barBuilder.OwnsOne(x => x.Baz);
        });
    });
}

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