0

.NET Core 2.2:

I have a table-per-hierarchy (TPH) inheritance and I have customer and agent as inheriting from membership. Now I try and create a purchase order and want to add both their membership Ids as foreign keys but I can't, because that would mean putting MemberID twice as foreign keys in my code first model:

    [ForeignKey("CustomerModel")]
    public string MemberId { get; set; }

    [ForeignKey("AgentModel")]
    public string MemberId { get; set; }

Do I have to split these into two tables instead of using inheritance? Can I just rename one of these from MemberId to AgentID for example or does it need to match the foreign key's name?

jps
  • 20,041
  • 15
  • 75
  • 79
John Ashmore
  • 1,015
  • 1
  • 10
  • 25
  • "Now I try and create a purchase order and want to add both their membership Ids as foreign keys but I cant, because that would mean putting MemberID twice as foreign keys " As those generally refer to different members, that is not a problem. Usually you give teh name some telltale. Like "FK_AgentMemberId" and "FL_MemberMemberID" – Christopher Nov 10 '19 at 03:32

1 Answers1

0

In Databases it can rarely happen that you have several similar entries. Like Multiple Foreign keys from the same table. In these rare cases, they are also not a full N:M relationship, so they can not (and should not) be resolved like that. You have a valid examples for this rare case. After all, a Agent can also be a Customer in any given Transaction. Including ones where he is also the agent.

I always try to work from wich table a Foreign Key is, into the Column names. But in such a case, you could work the function into it.

public string Fk_CustomerMemberId { get; set; }

public string Fk_AgentMemberId { get; set; }

I have no idea how to get EF to properly map them given the different names, however. I would guess you put that into the [ForeignKey("CustomerModel")] Atribute. Maybe something like ("CustomerModel.MemberID") or ("CustomerModel","MemberID")?

Christopher
  • 9,634
  • 2
  • 17
  • 31