EF Navigational Property through Different Properties Other Than Key

Question

I'm working on mapping a legacy application with classes and use EntityFramework against it.

One flaw I have found in this legacy database is that multiple tables refer to a specific table through 2 different fields.

I'm not sure if this is possible and why I can't seem to find anything about it so I am here.

Here is a visual sample:

public class Term {
    [Key]
    public string Id { get; set; } // sample value: "12-34-56/78"
    public string CleanId { get; set; } // sample value: "12345678" (basically the Id without special characters)
    public DateTime Date { get; set; }
}

public class App {
    public int Id { get; set; }
    public string CleanTermId { get; set; } // foreign key is in Term class using the `CleanId` field
}

public class Question {
    public int Id { get; set; }
    public string TermId { get; set; } // foreign key is in Term class using the `Id` field
}

How can I properly add a navigational property from App and Question to the Term class using either DataAnnotations (preferred) to Fluent API? I do not require a navigational property from Term to App or Question but it's ok if your answer includes it.

Let me know if this is not clear.

Answer 1

Joining on fields other than Primary Key was something that isnt supported in EF versions prior to EF Core, however with your mention of it being a legacy app I doubt you would want to overhaul it to be able to use EF Core.

There was a User Voice request for the feature to be added Here which the response is that they had no plans to add this functionality into EF6 - so Core would be the only way to really do this.

In terms of your classes you would be able to link Question and Term as its based PK - FK, but the App to Term is basing both on non-PK fields, even with a Unique constraint on the DB, this is something not supported in EF prior to Core