如何配置Professional与Agenda的一对多实体映射（Agenda必含Professional）

需求说明 #

• 每个Agenda必须关联一个Profissional
• 同一Profissional可对应多个Agenda

当前实体类定义 #

public class Profissional
{
    public GuidId Id { get; set; }

    public StatusEnum Status { get; set; }

    public string? Nome { get; set; }
}

public class ProfissionalAgenda : Profissional
{
    public GuidId AgendaId { get; set; }//Foreign key

    public virtual Agenda Agenda { get; set; } = null!;//navigation property
}

public class Agenda
{
    public GuidId Id { get; set; }

    public string NomeAgenda { get; set; }
    
    public virtual ProfissionalAgenda? Profissional { get; set; }

    public StatusEnum Status { get; set; }
}

当前映射配置 #

public class ProfissionalConfiguration : IEntityTypeConfiguration<Profissional>
{
    public void Configure(EntityTypeBuilder<Profissional> builder)
    {
        builder.HasKey(x => x.Id);
    }
}

public class AgendaConfiguration : IEntityTypeConfiguration<Agenda>
{
    public void Configure(EntityTypeBuilder<Agenda> builder)
    {
        builder.HasKey(x => x.Id);

        builder
            .HasOne(x => x.Profissional)
            .WithOne(x => x.Agenda)
            .HasForeignKey<ProfissionalAgenda>(x => x.AgendaId);
    }
}

当前配置的问题 #

当前配置使用HasOne().WithOne()设置了一对一关联，导致生成的Profissionais表中新增了AgendaId外键，这限制了一个Profissional只能对应一个Agenda，完全不符合一对多的需求。

修正方案 #

1. 调整实体类设计 #

移除错误的继承关系，明确一对多的导航属性：

public class Profissional
{
    public GuidId Id { get; set; }
    public StatusEnum Status { get; set; }
    public string? Nome { get; set; }

    // 导航属性：一个Profissional对应多个Agenda
    public virtual ICollection<Agenda> Agendas { get; set; } = new List<Agenda>();
}

public class Agenda
{
    public GuidId Id { get; set; }
    public string NomeAgenda { get; set; }
    public StatusEnum Status { get; set; }

    // 外键：关联Profissional的Id（必填，确保每个Agenda必须关联Profissional）
    public GuidId ProfissionalId { get; set; }
    // 导航属性：一个Agenda属于一个Profissional
    public virtual Profissional Profissional { get; set; } = null!;
}

2. 修正映射配置 #

将一对一关联改为一对多配置：

public class ProfissionalConfiguration : IEntityTypeConfiguration<Profissional>
{
    public void Configure(EntityTypeBuilder<Profissional> builder)
    {
        builder.HasKey(x => x.Id);

        // 配置一对多关联：Profissional拥有多个Agenda，Agenda必须关联Profissional
        builder.HasMany(p => p.Agendas)
               .WithOne(a => a.Profissional)
               .HasForeignKey(a => a.ProfissionalId)
               .IsRequired(); // 强制外键不为空，满足Agenda必须关联Profissional的要求
    }
}

public class AgendaConfiguration : IEntityTypeConfiguration<Agenda>
{
    public void Configure(EntityTypeBuilder<Agenda> builder)
    {
        builder.HasKey(x => x.Id);
        builder.Property(x => x.NomeAgenda).IsRequired(); // 确保Agenda名称必填
    }
}

3. 修正后生成的SQL #

调整后的配置会生成符合需求的数据库表结构：

CREATE TABLE [dbo].[Profissionais](
    [Id] [uniqueidentifier] NOT NULL,
    [Status] [int] NOT NULL,
    [Nome] [nvarchar](max) NULL,
    CONSTRAINT [PK_Profissionais] PRIMARY KEY ([Id])
)

CREATE TABLE [dbo].[Agendas](
    [Id] [uniqueidentifier] NOT NULL,
    [NomeAgenda] [nvarchar](max) NOT NULL,
    [Status] [int] NOT NULL,
    [ProfissionalId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Agendas] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Agendas_Profissionais_ProfissionalId] FOREIGN KEY ([ProfissionalId]) REFERENCES [dbo].[Profissionais]([Id]) ON DELETE CASCADE
)

该结构满足：

• Agendas表的ProfissionalId为必填项，确保每个Agenda都关联一个Profissional
• 多个Agenda可以使用同一个ProfissionalId，实现一个Profissional对应多个Agenda的需求

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