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.

Please show `ProspectDto` and `PersonDto` class declaration (you may skip data fields, not related to question). — Dmitry, Dec 21 '16 at 13:21
Hmm, can't see any `ID` field... Guess it may exist in `DtoBase`... Can you please provide all info about your model, related to question? — Dmitry, Dec 21 '16 at 13:36

score 21 · Accepted Answer · answered Dec 21 '16 at 22:30

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.

It works perfectly, thanks. Other question, do you know the equivalent of this using fluent API? — Bruno Joaquim, Dec 22 '16 at 11:00
Surprised not more upvotes on this - works perfectly and is a clear example. Cheers! — ubienewbie, Mar 24 '18 at 21:11

Dominik · Answer 2 · 2021-02-01T08:46:18.600

14

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);
}

edited Feb 01 '21 at 08:46

answered Apr 08 '19 at 19:27

Dominik

649
7
16

2

Dominik, you have forgotten .Entity() after modelBuilder. Then it works well. So, I'll vote up after fixing... ;-) – Tolbxela Jan 26 '21 at 17:21
1

@Tolbxela you are right, thank you! Just fixed it. – Dominik Feb 01 '21 at 08:46

score -2 · Answer 3 · answered Dec 21 '16 at 16:47

-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.

answered Dec 21 '16 at 16:47

Georg Patscheider

9,357
1
26
36

1

HasRequired() is not available on ef core. – Bruno Joaquim Dec 21 '16 at 19:07
Sorry, should have read your question more carefully, this code is from an EF 6.1 project. – Georg Patscheider Dec 21 '16 at 20:11

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

3 Answers3

Linked