How to have two different navigation properties of the same another entity type using attributes

Question

I have two entities:

//The master table/entity
[TABLE("POSITIONS")]
public class Position{
   [Key,Column("POSITIONID")]
   public int PositionId{get;set;}
   [Column("POSITIONNAME")]
   public string PositionName{get;set;}
}

//The detail table/entity
[TABLE("SLAVE_POSITIONS")]
public class SlavePosition{   
   [Key,Column("MASTERPOSID",Order=0)]
   public int MasterPosId{get;set;}
   [KEY,Column("SLAVEPOSID",Order=1)]
   public string SlavePosId{get;set;}

   [ForeignKey("MasterPosId")]
   public virtual Position MasterPosition {get;set;}
   [ForeignKey("SlavePosId")]
   public virtual Position SlavePosition {get;set;}
}

In the SlavePosition, as you can see, there two columns over which this entity is in FK relationship with Position. This layout works great. Now I also need to add this collection property to Position entity:

 public virtual ICollection<SlavePosition> SlavePositions{get;set;}

But apparently EF gets confused and I get {"ORA-00904: \"Extent1\".\"Position_PositionId\": invalid identifier"} error. If I declare it like this:

 [ForeignKey("SlavePositionId")]
 public virtual ICollection<SlavePosition> SlavePositions { get; set; }

and then fetch a Position with PositionId =1 like this:

 Position pos= dbContext.Positions.SingleOrDefault(x=>x.PositionId==1);

I get no error, but I get SlavePOsitions count 0, when it should be 5 because in the database I have 5 rows in the detail table. I am able to confirm this by running the below code:

IEnumerable<SlavePositions> slavePositions= dbcontext.SlavePositions.Where(x=>x.MasterPositionId==1);

I get five SlavePosition.

What should be the correct attribute for this collection property?

Answer 1

I finally figured it out. My mistake was in the referenced dependent property name. Instead of SlavePositionId I should put MasterPositionId.

This makes sense, because the Position entity acts as a master table and in real world Foreign Key relationship is set up on detail tables, not master ones. As there's no property in the dependent entity that has the same name as the PK in the master entity and there're more than one properties that have Foreignkey to the same master entity, EF needs more information.By specifying ForeignKey("MasterPositionId") to the ICollection navigation property, I instruct EF that Dependent end point property should be considered MasterPositionId. So I changed this

 [ForeignKey("SlavePositionId")]
 public virtual ICollection<SlavePosition> SlavePositions { get; set; }

to this

 [ForeignKey("MasterPositionId")]
 public virtual ICollection<SlavePosition> SlavePositions { get; set; }

In fact the former one itself is not wrong either, it just does not fit in this situation. But if I wanted to have a collection for MasterPositions this would fit perfectly fine.