EF Core: Using ID as Primary key and foreign key at same time

Question

I have two entities, Prospect and Person, what I'm trying to do is use Prospect.ID as the primary key on Prospect table and as the foreign key of PersonID, my idea is use the same ID for both entities without the need of a PersonID on my Prospect entity. When the prospect is being saved on database, it tries to save a PersonID even though I don't have this property on my Prospect entity, I would like to know if EF core supports this kind of relationship.

Here's what I got on my model builder:

modelBuilder.Entity<ProspectDto>(builder => { builder.ToTable("Prospects"); builder.HasKey(prospect => prospect.ID); });

modelBuilder.Entity<PersonDto>(builder => { builder.HasOne(p => p.Prospect).WithOne().HasForeignKey<ProspectDto>(pe => pe.ID); });

Here's what is being executed on database:

INSERT INTO [Prospects] ([ID], [PersonID]) VALUES (@p421, @p422)

PersonDto:

public class PersonDto : DtoBase
{
    public PersonDto()
    {
        
    }
  
    public ProspectDto Prospect { get; set; }
}

ProspectDto:

public class ProspectDto : DtoBase
{
    public ProspectDto()
    {

    }

    public PersonDto Person { get; set; } = new PersonDto();
}

DtoBase:

public abstract class DtoBase
{
    public Guid ID { get; protected set; }
}

Thanks.

Answer 1

Using attributes only, without FluentAPI:

public abstract class DtoBase
{
    [Key]
    public Guid ID { get; protected set; }
}

public class PersonDto : DtoBase
{
    [InverseProperty("Person")]
    public ProspectDto Prospect { get; set; }
}

public class ProspectDto : DtoBase
{
    [ForeignKey("ID")]           // "magic" is here
    public PersonDto Person { get; set; } = new PersonDto();
}

I don't know what is equivalent of ForeignKey in FluentAPI. All other (Key and InverseProperty) are configurable, but why use two methods instead one.

Code above generates following migration code:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Persons",
        columns: table => new
        {
            ID = table.Column<Guid>(nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Persons", x => x.ID);
        });

    migrationBuilder.CreateTable(
        name: "Prospects",
        columns: table => new
        {
            ID = table.Column<Guid>(nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Prospects", x => x.ID);
            table.ForeignKey(
                name: "FK_Prospects_Persons_ID",
                column: x => x.ID,
                principalTable: "Persons",
                principalColumn: "ID",
                onDelete: ReferentialAction.Cascade);
        });
}

Looks very close to what you need.

Answer 2

If you model the relationship as one:one, EF will automatically use the PK of the principal as FK for the dependent.

 ModelBuilder.Entity<ProspectDto>().HasRequired(p => p.Person).WithRequiredDependent();

Please note that ProspectDto will still have an ID column on the DB (inherited from DtoBase), but the FK relationship will be between ProspectDto.ID and PersonDto.ID and there should be no ProspectDto.PersonId column.

Answer 3

Here is the FluentAPI equivalent of @dmitry's solution:

// Model classes:
public abstract class DtoBase
{
    public Guid ID { get; protected set; }
}

public class PersonDto : DtoBase
{
    public ProspectDto Prospect { get; set; }
}

public class ProspectDto : DtoBase
{
    public PersonDto Person { get; set; } = new PersonDto();
}

-------------------------------------------------------------------

// DbContext's OnModelCreating override:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProspectDto>().HasOne(p => p.Person).WithOne().HasForeignKey<ProspectDto>(p => p.ID);
}