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.

Gibbon · Accepted Answer · 2018-11-06T16:17:23.583

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

edited Nov 06 '18 at 16:17

answered Nov 06 '18 at 16:12

Gibbon

2,633
1
13
19

Thanks. I guess that's why I haven't seen anything. Closest I've seen was using Fluent API and using `modelBuilder.Entity<>()MAP()` but I was unable to get it working properly. https://stackoverflow.com/questions/44550386/one-to-one-relationship-with-different-primary-key-in-ef-6-1-code-first – RoLYroLLs Nov 06 '18 at 16:23
@RoLYroLLs Hmm, ive not seen something that before - had the same kind of issues where I work though we recently did a major overhaul to .Net Core and EF Core though so thankfully resolved i. But yeah, Prior versions of EF, and even old Nhibernate are constrained by it having to join to the primary key sadly – Gibbon Nov 06 '18 at 16:26
The current way I'm able to solve it (which is dirty) is to have 2 classes: `Term` and `TermAlt` and use the appropriate one depending on which should be the key for the relationship. Any drawbacks doing this, before an overhaul? And yes, `CleanId` is unique. – RoLYroLLs Nov 06 '18 at 16:31
@RoLYroLLs It would depend I suppose, if you have them inheriting in a way where you can put them in the same collections etc making working with them fairly similar then I don't think its the end of the world. Also it really depends on how much work youre willing to put into the project, if its a legacy app that you would just like to maintain then an overhaul to work with Core would be a pretty heavy investment - though if its something you're planning future work on then it might be worth the time. – Gibbon Nov 06 '18 at 16:38

score 0 · Answer 2 · answered Nov 06 '18 at 16:21

0

Hi this is the correct Code:

    public class Term
{
    [Key]
    public string Id { get; set; }
    public string CleanId { get; set; }
    public DateTime Date { get; set; }
}

public class App
{
    public int Id { get; set; }

    [ForeignKey("CleanTermId")]
    public Term MyTerm { get; set; }
    public string CleanTermId { get; set; } 

}

public class Question
{
    public int Id { get; set; }

    [ForeignKey("TermId")]
    public Term MyTerm { get; set; }
    public string TermId { get; set; }
}

answered Nov 06 '18 at 16:21

abdouuuuu

1

2

This code wouldnt work in terms of the `App` class, it would be joining CleanTermId to the Id field of `Term` which would result in it always being null – Gibbon Nov 06 '18 at 16:24
1

Thanks, but as Gibbon said, this would only work if `App` to `Term` was using the "Key" property on `Term`, which it's not. – RoLYroLLs Nov 06 '18 at 16:26

EF Navigational Property through Different Properties Other Than Key

2 Answers2