EF Core 7查询返回结果时子对象Author为Null的问题

问题现象 #

调用/books接口获取图书列表时，每个作者的第一本图书对应的Author属性为null，但EF Core生成的SQL查询明明返回了所有行的完整关联数据。

数据定义 #

CREATE TABLE [dbo].[Authors]
(
    [AuthorId] NVARCHAR(100) NOT NULL,
    [Name] NVARCHAR(100) NULL,

    CONSTRAINT PK_Authors PRIMARY KEY (AuthorId)
)
GO

CREATE TABLE [dbo].[Books]
(
    [Id] INT NOT NULL IDENTITY,
    [WrittenBy] NVARCHAR(100) NOT NULL,
    [Title] NVARCHAR(100) NOT NULL,

    CONSTRAINT PK_Books PRIMARY KEY (Id),
    CONSTRAINT FK_Books_Author 
        FOREIGN KEY ([WrittenBy]) REFERENCES [Authors] ([AuthorId])
)
GO

INSERT INTO [Authors] ([AuthorId], [Name]) VALUES ('hawking', 'Hawking, Stephen')
INSERT INTO [Authors] ([AuthorId], [Name]) VALUES ('steinbeck', 'Steinbeck, John')
GO

INSERT INTO [Books] ([WrittenBy], [Title]) VALUES ('hawking', 'God Created the Integers')
INSERT INTO [Books] ([WrittenBy], [Title]) VALUES ('hawking', 'The Theory of Everything')
INSERT INTO [Books] ([WrittenBy], [Title]) VALUES ('hawking', 'A Brief History of Time')
INSERT INTO [Books] ([WrittenBy], [Title]) VALUES ('steinbeck', 'The Winter of Our Discontent')
INSERT INTO [Books] ([WrittenBy], [Title]) VALUES ('steinbeck', 'Burning Bright')
INSERT INTO [Books] ([WrittenBy], [Title]) VALUES ('steinbeck', 'A Life in Letters')
GO

模型代码 #

public class Author
{
    public required string AuthorId { get; set; }
    public required string Name { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public required string WrittenBy { get; set; }
    public required Author Author { get; set; }
}

public class BooksContext : DbContext
{
    public BooksContext(DbContextOptions<BooksContext> options)
        : base(options) { }

    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>(b =>
        {
            b.HasOne(x => x.Author).WithOne().HasForeignKey<Book>(x => x.WrittenBy).IsRequired();
        });

        modelBuilder.Entity<Author>(b =>
        {
            b.HasKey(x => x.AuthorId);
        });
    }
}

应用代码 #

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<BooksContext>(
    options => options.UseSqlServer(builder.Configuration.GetConnectionString("BooksDatabase"))
);

var app = builder.Build();

app.MapGet(
    "/books",
    async (BooksContext db) =>
    {
        return await db.Books.Include(b => b.Author).ToListAsync();
    }
);

app.Run();

生成的查询语句 #

SELECT [b].[Id], [b].[Title], [b].[WrittenBy], [a].[AuthorId], [a].[Name]
FROM [Books] AS [b]
INNER JOIN [Authors] AS [a] ON [b].[WrittenBy] = [a].[AuthorId]

返回结果 #

[
    {
        "id": 1,
        "title": "God Created the Integers",
        "writtenBy": "hawking",
        "author": null
    },
    {
        "id": 2,
        "title": "The Theory of Everything",
        "writtenBy": "hawking",
        "author": {
            "authorId": "hawking",
            "name": "Hawking, Stephen"
        }
    },
    {
        "id": 3,
        "title": "A Brief History of Time",
        "writtenBy": "hawking",
        "author": {
            "authorId": "hawking",
            "name": "Hawking, Stephen"
        }
    },
    {
        "id": 4,
        "title": "The Winter of Our Discontent",
        "writtenBy": "steinbeck",
        "author": null
    },
    {
        "id": 5,
        "title": "Burning Bright",
        "writtenBy": "steinbeck",
        "author": {
            "authorId": "steinbeck",
            "name": "Steinbeck, John"
        }
    },
    {
        "id": 6,
        "title": "A Life in Letters",
        "writtenBy": "steinbeck",
        "author": {
            "authorId": "steinbeck",
            "name": "Steinbeck, John"
        }
    }
]

问题原因与解决方法 #

原因 #

模型配置错误：将Book和Author的关系配置成了一对一（HasOne().WithOne()），但实际业务中一个作者对应多本图书，属于一对多关系。EF Core处理一对一关联映射时，会认为每个作者只能对应一本图书，导致同一作者的第一本图书关联映射异常，Author属性为null。

解决方法 #

修改OnModelCreating中的关系配置，将Author端的关系改为一对多：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>(b =>
    {
        // 配置Book到Author的多对一关系
        b.HasOne(x => x.Author)
          .WithMany() // 表示Author可以对应多个Book
          .HasForeignKey(x => x.WrittenBy)
          .IsRequired();
    });

    modelBuilder.Entity<Author>(b =>
    {
        b.HasKey(x => x.AuthorId);
    });
}

如果需要在Author类中添加图书集合属性，可显式配置双向关联：

// 更新Author类，添加Books集合
public class Author
{
    public required string AuthorId { get; set; }
    public required string Name { get; set; }
    public ICollection<Book> Books { get; set; } = new List<Book>();
}

// 更新关系配置
modelBuilder.Entity<Book>(b =>
{
    b.HasOne(x => x.Author)
      .WithMany(a => a.Books)
      .HasForeignKey(x => x.WrittenBy)
      .IsRequired();
});

修改后，EF Core会正确处理一对多关联的结果映射，所有图书的Author属性都会被正常填充。

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