EF Core .Include()无法加载关联数据问题求助

使用ASP.NET和EF Core开发基于.NET Core 7的REST API，数据库中User与Address为一对一关系，与Category为一对多关系。尝试加载User及其关联实体时，返回的User对象中Address和Categories字段均为null，程序无报错；移除.AsSplitQuery()不仅无法解决问题，还会导致程序报错。已确认实体类与数据库schema一致，Dapper查询可正常加载数据，需排查并解决EF Core无法加载关联数据的问题。

实体代码 #

User.cs #

public class User : AggregateRoot<UserId>
{
    public UserId Id { get; private set; }

    public Account Account { get; private set; }
    public Address Address { get; private set; }
    
    public readonly List<Category> Categories;
    public readonly List<CurrencyAccount> CurrencyAccounts;
    
    public UserEmail Email { get; private set; }
    public UserName Name { get; private set; }
    public UserPhoneNumber PhoneNumber { get; private set; }
    public UserGender Gender { get; private set; }
    public UserBirthDate BirthDate { get; private set; }
    
    internal User(UserId id, UserEmail email, UserName name, UserPhoneNumber phoneNumber,
        UserGender gender, UserBirthDate birthDate, Address address)
    {
        Id = id;
        Email = email;
        Name = name;
        PhoneNumber = phoneNumber;
        Gender = gender;
        BirthDate = birthDate;
        Address = address;
        
        AddEvent(new UserCreated(this));
    }

    private User()
    {

    }

    /* 聚合访问方法 */

}

Address.cs #

public class Address
{
    //private Regex universalPostalCodeRegex = new(@"^[a-z0-9][a-z0-9\- ]{0,10}[a-z0-9]$");
    // private Regex polandPostalCodeRegex = new(@"^\d{2}-\d{3}$");
    //private Regex houseNumberRegex = new(@"^\d{1,4}[A-Z]$");

    public AddressCountry Country{ get; }
    public AddressCity City { get; }
    public AddressStreet Street { get; }
    public AddressPostalCode PostalCode { get; }
    public AddressHouseNumber HouseNumber { get; }
    public AddressApartmentNumber? ApartmentNumber{ get; }

    public Address(string country, string city, string street, string postalCode, string houseNumber)
    {
        Country = country;
        City = city;
        Street = street;
        PostalCode = postalCode;
        HouseNumber = houseNumber;
    }

    private Address()
    {
    }
    
    public Address(string country, string city, string street, string postalCode, string houseNumber, string apartmentNumber) 
        : this(country, city, street, postalCode, houseNumber)
    {
        ApartmentNumber = apartmentNumber;
    }    
}

Category.cs #

public class Category
{
    public CategoryId Id { get; }
    public CategoryName Name { get; init; }

    public Category(CategoryId id, CategoryName name)
    {
        Id = id;
        Name = name;
    }
}

关系配置 #

//Address
builder
    .HasOne(u => u.Address)
    .WithOne()
    .HasForeignKey<Address>()
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

//Category
builder
    .HasMany(u => u.Categories)
    .WithOne()
    .IsRequired(false)
    .OnDelete(DeleteBehavior.Cascade);

EF Core生成的SQL #

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT u."Id", u."AccountId", u."BirthDate", u."Email", u."Gender", u."Name", u."PhoneNumber", a."Id", a."ApartmentNumber", a."City", a."Country", a."HouseNumber", a."PostalCode", a."Street", a."UserId"
      FROM "moneyDB"."Users" AS u
      LEFT JOIN "moneyDB"."Addresses" AS a ON u."Id" = a."UserId"
      ORDER BY u."Id", a."Id"
      LIMIT 2
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT c."Id", c."Name", c."UserId", t."Id", t."Id0"
      FROM (
          SELECT u."Id", a."Id" AS "Id0"
          FROM "moneyDB"."Users" AS u
          LEFT JOIN "moneyDB"."Addresses" AS a ON u."Id" = a."UserId"
          LIMIT 1
      ) AS t
      INNER JOIN "moneyDB"."Categories" AS c ON t."Id" = c."UserId"
      ORDER BY t."Id", t."Id0"

问题代码 #

public async Task<Domain.Aggregates.User> GetAsync(UserId id)
    => await _users
        .Include(u => u.Address )
        .Include(u => u.Categories)
        .AsSplitQuery()
        .SingleOrDefaultAsync();

解决方案 #

1. 修复查询过滤条件 #

问题代码未使用传入的id参数进行过滤，导致查询返回的是任意用户（或空），而非目标用户。需添加过滤条件：

public async Task<Domain.Aggregates.User> GetAsync(UserId id)
    => await _users
        .Include(u => u.Address )
        .Include(u => u.Categories)
        .AsSplitQuery()
        .SingleOrDefaultAsync(u => u.Id == id); // 添加id过滤

2. 初始化只读集合 #

User中的Categories是只读List且未初始化，EF Core无法向null集合中添加关联数据。需在私有构造函数中初始化集合：

private User()
{
    Categories = new List<Category>();
    CurrencyAccounts = new List<CurrencyAccount>();
}

3. 修复Address实体的映射问题 #

Address类的属性为值对象类型（AddressCountry、AddressCity等），但构造函数接收的是string参数，EF Core无法自动完成字符串到值对象的转换。需：

• 修改Address构造函数，接收值对象类型参数：

public Address(AddressCountry country, AddressCity city, AddressStreet street, AddressPostalCode postalCode, AddressHouseNumber houseNumber)
{
    Country = country;
    City = city;
    Street = street;
    PostalCode = postalCode;
    HouseNumber = houseNumber;
}

public Address(AddressCountry country, AddressCity city, AddressStreet street, AddressPostalCode postalCode, AddressHouseNumber houseNumber, AddressApartmentNumber? apartmentNumber) 
    : this(country, city, street, postalCode, houseNumber)
{
    ApartmentNumber = apartmentNumber;
}

• 在EF Core配置中为每个值对象添加转换规则：

// 以AddressCountry为例，其他值对象同理配置
builder.Entity<Address>()
    .Property(a => a.Country)
    .HasConversion(
        value => value.Value, // 将值对象转换为数据库存储的字符串
        dbValue => new AddressCountry(dbValue)); // 将数据库字符串转换为值对象

4. 排查AsSplitQuery报错问题 #

移除.AsSplitQuery()后报错，大概率是因为未过滤时返回数据量过大导致笛卡尔积爆炸。添加id过滤条件后，数据量大幅减少，可尝试移除.AsSplitQuery()验证是否正常。

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