CODEWITHSUNDEEP
asp.net-mvcentity-frameworkfluent-interface
Drake
Drake

Reputation: 165

Entity Framework Mapping. Multiple Foreign keys

I have two tables 

People                  Relation
-------------           -----------------
Id (int)                Id (int)
Name (string)           ParentPeopleId (int)
                        ChildPeopleId (int)

I need to get all people by Relation table with union all. The relation table has two foreign keys. And there is one problem with mapping them. The mapping is one to many. People has many Relation, Relation has one People.

I mapped them like this:

HasRequired(r=> r.People).WithMany(p=>p.Relation).HasForeignKey(r=>r.ChildPeopleId);

So, how can I map the second foreign key?

Upvotes: 1

Views: 2085

Answers (1)

ocuenca
ocuenca

Reputation: 39326

Per each FK column in your Relations table you should have a navigation property in your Relation entity (this is not mandatory, but what is mandatory is have at least one navigation property between the entities involve in the relationship). In this case you have two relationships between People and Relations, and a navigation property represents one end in an relationship. Your model could be this way:

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

  public int ParentPeopleId {get;set;}

  public int ChildPeopleId {get;set;}

  public virtual People ParentPeople {get;set;}
  public virtual People ChildPeople {get;set;}
}
public class People
{
   public int Id {get;set;}
   public string Name {get;set;}

   public virtual ICollection<Relation> ParentRelations {get;set;}
   public virtual ICollection<Relation> ChildRelations {get;set;}

}

And the Fluent Api configurations like this:

HasRequired(r=> r.ParentPeople ).WithMany(p=>p.ParentRelations ).HasForeignKey(r=>r.ParentPeopleId);   
HasRequired(r=> r.ChildPeople).WithMany(p=>p.ChildRelations ).HasForeignKey(r=>r.ChildPeopleId );

Now if you don't want to work with one of the collection navigation properties in your People entity, you can create an unidirectional relationship. For example if you don't want ParenRelations navigation property, you can configure that relationship as follow:

HasRequired(r=> r.ParentPeople).WithMany().HasForeignKey(r=>r.ParentPeopleId);

Update

Let me start first with a suggestion. I thing your table Relation is not playing any role is you have only those columns. If a person con only have a parent I would change your model to the following:

public class People
{
   public int Id {get;set;}
   public string Name {get;set;}

   public int ParentId {get;set;}
   public virtual People Parent {get;set;}
   public virtual ICollection<People> Children {get;set;}
}

And you relationship configuration would be:

HasOptional(r=> r.Parent).WithMany(p=>p.Children).HasForeignKey(r=>r.ParentId);

Now going back to your current model, EF sees your ChildPeopleId property as an simple scalar column, it doesn't know it's a FK column, that's way I suggested above map two relationship instead one.

Another thing, with the below line 

var Peoplelist = MyDbContext.People.Include(p=>p.Relations.Select(r=>r.People)).ToList();

You are telling to EF that you want to load the Relation entities related to a People, but also you want to load the People related with each Relation, which is at the same time the same People where the Relation came from, so, you don't need to do the last select, if your data is properly related, that People navigation property is going to be loaded when you execute your query,so, that query should be this way:

var Peoplelist = MyDbContext.People.Include(p=>p.Relations).ToList();

Upvotes: 1

Related Questions