Can I have two foreign keys from the same table key in a table-per-hierarchy inheriting table somehow?

Question

.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?

Answer 1

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")?