Configuring one-to-many foreign key to a non primary key property

Question

This maybe an easy question but I didn't manage to figure it out after hours of reading Microsoft documentation. I have following 2 models in my DB (I simplified them for better understanding):

public class Schedule
{
    public int Id { get; set; }
    ...
    public string Role { get; set; }
}

public class Roles
{
    public int Id { get; set; }
    ...
    public string RoleDisplay { get; set; }
}

I need to set up Role string from Schedule model as a foreign key with one-to-many relation to RoleDisplay key in Roles model. So in Schedule we can have a lot of records with Role cell pointing to the Roles table.

If it was regular Id key I would add

public Roles Roles { get; set; }

to Schedule and EF would automatically generate corresponding RoleID column in the Schedule table.

The issue is that I'm not dealing with primary key and the key naming convention doesn't match standard

<TableName><ColumnName> 

I heard that this can be set up in Fluent API, something like:

modelBuilder.Entity<Schedule>()
    .HasOne()
    .WithMany()
    .HasForeignKey();

but I didn't figure out what needs to be put in brackets for each statement.

Can you please help me?

Answer 1

Since you don't have any navigation property on either entity, you have to use the generic version HasOne<Roles>() to indicate which table the foreign-key refers to. Once that is clear to EF, you don't need to pass anything to WithMany(). Try the following -

1. Declare RoleDisplay as an alternate-key on Roles -

modelBuilder.Entity<Roles>(e =>
{
    e.HasKey(p => p.Id);
    e.HasAlternateKey(p => p.RoleDisplay);
});

2. While configuring the foreign-key on Schedule, specify which key on Roles it should target -

modelBuilder.Entity<Schedule>(e =>
{
    e.HasKey(p => p.Id);
});
modelBuilder.Entity<Schedule>()
    .HasOne<Roles>()
    .WithMany()
    .HasForeignKey(p => p.Role)
    .HasPrincipalKey(p => p.RoleDisplay);

Hope that helps.