Create Foreign Key to a Unique Key in another Entity with EF Core

Question

I have a Program entity like this

public class Program : IEntityBase
{
    public int Id { get; set; }
    public string ProgramCode { get; set; }
    public string Name { get; set; }
    public int DegreeTypeID { get; set; }
    public DegreeType DegreeType { get; set; }
}

with programCode created as a unique key with this implementation

protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        builder.Entity<Program>().HasAlternateKey(d => d.ProgramCode).HasName("AK_ProgramCode");
    }

I have another entity ApplicantProgram with this definition

public class ApplicantProgram : IEntityBase
{
    public int Id { get; set; }
    public int ApplicantID { get; set; }
    public Applicant Applicant { get; set; }
    [Required]
    public string FirstChoiceID { get; set; }
    [Required]
    public string SecondChoiceID { get; set; }
    [Required]
    public string ThirdChoiceID { get; set; }
    public string SessionID { get; set; }
}

Which has FirstChoiceID, SecondChoiceID & ThirdChoiceID having ProgramCode in the program table. Now these are my questions,

1. How do i get Program.Name property from ApplicantProgram knowing the FirstChoiceID that is to link to Program.ProgramCode?
2. Is it possible to create a Navigation property to program from ApplicantProgram?
3. How do i create a foreign key from ApplicantProgram to Program based off the ChoiceIDs that should link to Program.ProgramCode without using Program.Id?

Thank you for pausing to read this.

Answer 1

(1) How do I get Program.Name property from ApplicantProgram knowing the FirstChoiceID that is to link to Program.ProgramCode?

There is nothing specific to EF here, you could use the typical data correlation operator - join. Just because you have 3 related properties, you would need 3 joins as well:

var query =
    from applicantProgram in db.ApplicantPrograms
    join firstChoice in db.Programs on applicantProgram.FirstChoiceID equals firstChoice.ProgramCode
    join secondChoice in db.Programs on applicantProgram.SecondChoiceID equals secondChoice.ProgramCode
    join thirdChoice in db.Programs on applicantProgram.ThirdChoiceID equals thirdChoice.ProgramCode
    select new
    {
        ApplicantProgram = applicantProgram,
        FirstChoice = firstChoice,
        SecondChoice = secondChoice,
        ThirdChoice = thirdChoice,
    };

Inside the select, you could get the whole related objects as above, or specific properties like firstChoice.Name, secondChoice.Name etc.

But you won't need all that in EF once you define the navigation properties, which leads us to:

(2) Is it possible to create a Navigation property to program from ApplicantProgram?

(3) How do I create a foreign key from ApplicantProgram to Program based off the ChoiceIDs that should link to Program.ProgramCode without using Program.Id?

These two are interrelated. While it's possible to define a FK without navigation property, the navigation property would allow you simple access to related entity properties inside the LINQ queries as well as simple eager loading the related entity as part of the entity which is using it.

Start by adding the 3 navigation properties (one for each FK property) in ApplicantProgram class:

public Program FirstChoice { get; set; }
public Program SecondChoice { get; set; }
public Program ThirdChoice { get; set; }

and the following fluent configuration:

builder.Entity<ApplicantProgram>()
    .HasOne(e => e.FirstChoice)
    .WithMany()
    .HasForeignKey(e => e.FirstChoiceID)
    .HasPrincipalKey(e => e.ProgramCode)
    .OnDelete(DeleteBehavior.Restrict);

builder.Entity<ApplicantProgram>()
    .HasOne(e => e.SecondChoice)
    .WithMany()
    .HasForeignKey(e => e.SecondChoiceID)
    .HasPrincipalKey(e => e.ProgramCode)
    .OnDelete(DeleteBehavior.Restrict);

builder.Entity<ApplicantProgram>()
    .HasOne(e => e.ThirdChoice)
    .WithMany()
    .HasForeignKey(e => e.ThirdChoiceID)
    .HasPrincipalKey(e => e.ProgramCode)
    .OnDelete(DeleteBehavior.Restrict);

What we have here is the standard many-to-one relationship configuration - with HasOne(...) specifying the reference navigation property, WithMany() specifying no corresponding collection navigation property, HasForeighKey(...) specifying the corresponding FK property, and also the typical for multiple relationships to one and the same table turning off the cascade delete in order to avoid the multiple cascade paths problem.

What is specific thought (and is the EF Core improvement over EF6) is the HasPrincipalKey(...) method which allows you to specify other unique key property instead of the PK (by default) to be used by the FK relationship. Which in the combination with HasAlternateKey(...) on the other end allows to achieve the desired FK relationship setup.

And basically that's all. Now the query from (1) could be simply

var query =
    from applicantProgram in db.ApplicantPrograms
    select new
    {
        applicantProgram,
        firstChoice = applicantProgram.FirstChoice,
        secondChoice = applicantProgram.SecondChoice,
        thirdChoice = applicantProgram.ThirdChoice,
    };

Similar to (1), you could project the whole related objects or just properties you need.

Or you could get the ApplicantProgram instances with populated related Program properties by adding Include operators to the ApplicantProgram query (the so called eager loading):

var query = db.ApplicantPrograms
    .Include(applicantProgram => applicantProgram.FirstChoice)
    .Include(applicantProgram => applicantProgram.SecondChoice)
    .Include(applicantProgram => applicantProgram.ThirdChoice);