Custom foreign key column of source table

Question

I would like to configure EFCore that A property of class B would be loaded based on property AAlternateId and AAlteranateId should be foreign key of table A of column AlternateId. How would I configure that using fluent api?

class A {
  public Guid Id { get; set; }

  public Guid AlternateId { get; set; }
}

class B {
  public Guid Id { get; set; }

  public AAlternateId { get; set; }

  public A A { get; set; }
}

Does this answer your question? [Mapping foreign key to non primary surrogate key column in EF code first](https://stackoverflow.com/questions/38029313/mapping-foreign-key-to-non-primary-surrogate-key-column-in-ef-code-first) — Flater, Aug 04 '20 at 12:56

score 0 · Answer 1 · answered Aug 05 '20 at 07:12

First, you seem forget to set the type of AAlternateId.

public Guid AAlternateId { get; set; }

Then, if you want the ForeignKey AAlternateId to be linked to AlternateId (which is not PK), you should use PrincipalKey to define the reference key AlternateId with a unique restriction.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<A>().ToTable("A");
    modelBuilder.Entity<B>().ToTable("B");
    modelBuilder.Entity<B>()
        .HasOne(b => b.A)
        .WithOne()
        .HasPrincipalKey<A>(a => a.AlternateId)
        .HasForeignKey<B>(b => b.AAlternateId);
}

And the result:

CREATE TABLE [dbo].[B] (
[Id]           UNIQUEIDENTIFIER NOT NULL,
[AAlternateId] UNIQUEIDENTIFIER NOT NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_B_A_AAlternateId] FOREIGN KEY ([AAlternateId]) REFERENCES [dbo].[A] ([AlternateId]) ON DELETE CASCADE
);

Hi @Puchacz, can this solution help you solve the problem, if so, you can mark it as answer, thanks:) — mj1313, Aug 10 '20 at 09:50

Custom foreign key column of source table

1 Answers1