EF Build self relationship

Question

this time i have a simple question:

I have one table, related with himself

=====================
| Id                |  
| IdParent          |
| NodeName          | 
=====================

Where IdParent Could Have 0 or many Id. The question is, how can i configure this relationship using FluentApi or data anotations.

The result of this relationship is a tree (with two levels) like this:

a
|---b
|---c 
d
e
f---g

where (a,d,e,f) are parent nodes and (b,c,g) are child nodes.

Very Tks for your help.

score 3 · Accepted Answer · edited May 23 '17 at 11:45

Entity framework supports self-referencing foreign keys. It's not clear if you are a database first, or codefirst person.

If you're a database first person, just create a self-referencing foreign key in your database, the update the model from the DB. EF has supported this style of key since at least EF 4.

From a code first perspective, it may a bit trickier. If you're using EF 4.x you need to override the OnModelCreating function (see example here). If you're using EF 5 (from the .net 4.5 beta, or the June 2011 CTP), then this question provides some good guidance. The short of it is to use [ForeignKey("IdParent")] attribute on your model.

Either way you should see XML something like ...

  <edmx:Runtime>
    <edmx:StorageModels>
    <Schema Namespace="...">
          <AssociationSet Name="FK_Culture_has_ParentCulture" 
                   Association="AL_Model.Store.FK_Culture_has_ParentCulture">
            <End Role="Culture" EntitySet="Culture" />
            <End Role="Culture1" EntitySet="Culture" />
          </AssociationSet>

 .....
 .....
 <edmx:ConceptualModels>
    <Schema Namespace="...">
    <EntityContainer Name="..." >
      <Association Name="FK_Culture_has_ParentCulture">
        <End Role="Culture" Type="AL_Model.Store.Culture" Multiplicity="0..1" />
        <End Role="Culture1" Type="AL_Model.Store.Culture" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Culture">
              <PropertyRef Name="CultureID" />
            </Principal>
            <Dependent Role="Culture1">
              <PropertyRef Name="ParentCultureID" />
            </Dependent>
          </ReferentialConstraint>
      </Association>

Self-Referencing Structure

This statement warrents a little attention:

Where IdParent Could Have 0 or many Id.

In order to have "zero, or a parentID" then you're going to need to have an entry in your table containing zero as an ID. Why? because foreign keys need to link to something. What I suspect you want, however, is to make IdParent nullable. Your question doesn't specify database, but most major databases enforce foreign keys only when there is a value. Said another way, Null in IdParent means there is no parent.

Tks for your help. My app is db first. There is any way to do that with fluent API ? — Juan Pablo Gomez, Mar 18 '12 at 19:03

score 1 · Answer 2 · answered Mar 26 '12 at 22:18

After many tries.. the best solution was extracted from the first link provided for EBarr example here).

The last solution for me was:

Add Icollection to my table:

public partial class myTable
{
    public myTable()
    {
        this.Ids= new HashSet< myTable >();
    }
    public decimal id {get;set;}
    public decimal idParent {get;set;}
    public string  NodeName {get;set;}

    public Icollection<myTable> Ids {get;set;}
}

Then using Fluent Api

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<myTable>()
        .HasMany(x => x.Ids)
        .WithOptional()
        .HasForeignKey(m => m.IdParent);
}

EF Build self relationship

2 Answers2

Linked