EF Core：如何配置User、Company、Property三实体关联关系？

一、补充实体类必要字段 #

首先需要给Company补充关联User的外键和导航属性，满足「企业属于某个用户」的需求：

public class Company : BaseEntity
{
    public string CompanyName { get; set; } = string.Empty;
    public Guid OwnerUserId { get; set; } // 关联User的外键
    public User OwnerUser { get; set; } // 所属User的导航属性
    public Property CompanyProperties { get; set; }
}

二、用FluentAPI配置实体关联 #

在DbContext的OnModelCreating方法中完成所有关联关系配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 1. User与Company的一对多关系
    modelBuilder.Entity<User>()
        .HasMany(u => u.Companies)
        .WithOne(c => c.OwnerUser)
        .HasForeignKey(c => c.OwnerUserId)
        .OnDelete(DeleteBehavior.Cascade); // 删除用户时级联删除其名下企业

    // 2. User与Property的一对一关系（显式外键版本）
    // 先给Property类添加外键字段：public Guid? UserId { get; set; } 和 public User? User { get; set; }
    modelBuilder.Entity<User>()
        .HasOne(u => u.Properties)
        .WithOne(p => p.User)
        .HasForeignKey<Property>(p => p.UserId)
        .OnDelete(DeleteBehavior.Cascade);

    // 3. Company与Property的一对一关系（显式外键版本）
    // 给Property类添加外键字段：public Guid? CompanyId { get; set; } 和 public Company? Company { get; set; }
    modelBuilder.Entity<Company>()
        .HasOne(c => c.CompanyProperties)
        .WithOne(p => p.Company)
        .HasForeignKey<Property>(p => p.CompanyId)
        .OnDelete(DeleteBehavior.Cascade);
}

如果不想在Property类中显式定义外键，也可以使用EF Core的隐式外键：

// User与Property一对一（隐式外键）
modelBuilder.Entity<User>()
    .HasOne(u => u.Properties)
    .WithOne()
    .HasForeignKey<Property>("UserId")
    .OnDelete(DeleteBehavior.Cascade);

// Company与Property一对一（隐式外键）
modelBuilder.Entity<Company>()
    .HasOne(c => c.CompanyProperties)
    .WithOne()
    .HasForeignKey<Property>("CompanyId")
    .OnDelete(DeleteBehavior.Cascade);

三、解决Dictionary<string, object>存储问题 #

EF Core原生不支持直接存储该类型，提供两种可行方案：

方案1：序列化为JSON字符串存储 #

通过ValueConverter将字典序列化为JSON，存储到数据库的字符串字段中，适合不需要SQL查询自定义属性的场景：

modelBuilder.Entity<Property>()
    .Property(p => p.Properties)
    .HasConversion(
        dict => System.Text.Json.JsonSerializer.Serialize(dict, null),
        json => System.Text.Json.JsonSerializer.Deserialize<Dictionary<string, object>>(json, null)
    )
    .HasColumnType("nvarchar(max)");

方案2：拆分为子实体存储 #

如果需要通过SQL查询自定义属性，可以将字典拆分为PropertyItem子实体：

1. 创建子实体类：

public class PropertyItem : BaseEntity
{
    public Guid PropertyId { get; set; }
    public Property ParentProperty { get; set; }
    public string Key { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;
    public string ValueType { get; set; } = string.Empty; // 存储值的类型，用于后续转换
}

2. 修改Property类：

public class Property : BaseEntity
{
    public ICollection<PropertyItem> PropertyItems { get; set; } = new List<PropertyItem>();

    // 业务层用的只读字典属性
    public Dictionary<string, object> Properties
    {
        get => PropertyItems.ToDictionary(
            pi => pi.Key,
            pi => Convert.ChangeType(pi.Value, Type.GetType(pi.ValueType))
        );
        set
        {
            PropertyItems = value.Select(kv => new PropertyItem
            {
                Key = kv.Key,
                Value = kv.Value.ToString(),
                ValueType = kv.Value.GetType().AssemblyQualifiedName
            }).ToList();
        }
    }
}

3. 配置子实体关联：

modelBuilder.Entity<Property>()
    .HasMany(p => p.PropertyItems)
    .WithOne(pi => pi.ParentProperty)
    .HasForeignKey(pi => pi.PropertyId)
    .OnDelete(DeleteBehavior.Cascade);

四、完整DbContext示例 #

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Company> Companies { get; set; }
    public DbSet<Property> Properties { get; set; }
    public DbSet<PropertyItem> PropertyItems { get; set; } // 方案2需要添加

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("你的数据库连接字符串");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 一对多：User -> Company
        modelBuilder.Entity<User>()
            .HasMany(u => u.Companies)
            .WithOne(c => c.OwnerUser)
            .HasForeignKey(c => c.OwnerUserId)
            .OnDelete(DeleteBehavior.Cascade);

        // 一对一：User <-> Property
        modelBuilder.Entity<User>()
            .HasOne(u => u.Properties)
            .WithOne(p => p.User)
            .HasForeignKey<Property>(p => p.UserId)
            .OnDelete(DeleteBehavior.Cascade);

        // 一对一：Company <-> Property
        modelBuilder.Entity<Company>()
            .HasOne(c => c.CompanyProperties)
            .WithOne(p => p.Company)
            .HasForeignKey<Property>(p => p.CompanyId)
            .OnDelete(DeleteBehavior.Cascade);

        // 方案1：Dictionary转JSON
        modelBuilder.Entity<Property>()
            .Property(p => p.Properties)
            .HasConversion(
                dict => System.Text.Json.JsonSerializer.Serialize(dict, null),
                json => System.Text.Json.JsonSerializer.Deserialize<Dictionary<string, object>>(json, null)
            )
            .HasColumnType("nvarchar(max)");

        // 方案2：配置Property与PropertyItem的一对多
        // modelBuilder.Entity<Property>()
        //     .HasMany(p => p.PropertyItems)
        //     .WithOne(pi => pi.ParentProperty)
        //     .HasForeignKey(pi => pi.PropertyId)
        //     .OnDelete(DeleteBehavior.Cascade);
    }
}

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