EF4.1 Code first one to many self reference

Question

I'm trying to make a one to many self reference using EF4.1 code first. My entity looks like this

public class Site
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Sitename { get; set; }

    public virtual Site ChildSite { get; set; }
    public virtual ICollection<Site> ChildSites { get; set; }
}

And in my context class I do this to make the self reference

modelBuilder.Entity<Site>()
    .HasOptional(s => s.ChildSite)
    .WithMany(s => s.ChildSites)
    .HasForeignKey(s => s.ParentId);

But when i try to add some dummy data to my database with this code

var sites = new List<Site>
{
    new Site { ParentId = 0, Sitename = "Top 1" },
    new Site { ParentId = 0, Sitename = "Top 2" },
    new Site { ParentId = 0, Sitename = "Top 3" },
    new Site { ParentId = 0, Sitename = "Top 4" },
    new Site { ParentId = 1, Sitename = "Sub 1_5" },
    new Site { ParentId = 1, Sitename = "Sub 1_6" },
    new Site { ParentId = 1, Sitename = "Sub 1_7" },
    new Site { ParentId = 1, Sitename = "Sub 1_8" },
    new Site { ParentId = 2, Sitename = "Sub 2_9" },
    new Site { ParentId = 2, Sitename = "Sub 2_10" },
    new Site { ParentId = 2, Sitename = "Sub 2_11" },
    new Site { ParentId = 2, Sitename = "Sub 2_12" },
    new Site { ParentId = 3, Sitename = "Sub 3_13" },
    new Site { ParentId = 3, Sitename = "Sub 3_14" },
    new Site { ParentId = 3, Sitename = "Sub 3_15" },
    new Site { ParentId = 3, Sitename = "Sub 3_16" },
    new Site { ParentId = 4, Sitename = "Sub 4_17" },
    new Site { ParentId = 4, Sitename = "Sub 4_18" },
    new Site { ParentId = 4, Sitename = "Sub 4_19" },
    new Site { ParentId = 4, Sitename = "Sub 4_20" }
};
sites.ForEach(s => context.Sites.Add(s));
context.SaveChanges();

I get this error :

Unable to determine the principal end of the 'Cms.Model.Site_ChildSite' relationship. Multiple added entities may have the same primary key.

What am i missing here ?

Edit :

Here is the solution to my problem. I removed the public virtual Site ChildSite { get; set; } from the entity

public class Site
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Sitename { get; set; }

    public virtual ICollection<Site> ChildSites { get; set; }
}

I then changed the modelBuilder to this

modelBuilder.Entity<Site>()
    .HasMany(s => s.ChildSites)
    .WithOptional()
    .HasForeignKey(s => s.ParentId);

As it seems that auto generation of the database did not work I went ahead and created the table myself like this

int Id, not null, primary key
int ParentId, null
string Sitename, null

And everything works as I want it to.

2nd Edit

And the final step to get the auto generation of dummy data to work

var parent1 = new Site
{
    Sitename = "Top 1",
    ChildSites = new List<Site>
        {
            new Site {Sitename = "Sub 1_5"},
            new Site {Sitename = "Sub 1_6"},
            new Site {Sitename = "Sub 1_7"},
            new Site {Sitename = "Sub 1_8"}
        }
};

ect . . . 

context.Sites.Add(parent1);
context.SaveChanges();

See Slauma answer below

Answer 1

I have a different model, but I will post what was necessary in my case and it works just fine with EF4.1 on MVC 3:

Model

public class Page
{
    public int Id { get; set; }
    public virtual string Title { get; set; }

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

Database (use of fluent API)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     modelBuilder.Entity<Page>()
                 .HasOptional(s => s.Parent)
                 .WithMany(s => s.Children)
                 .HasForeignKey(s => s.ParentId);
}

Initializer

var page1 = new Page()
{
     Title = "title"
};

context.Pages.Add(page1);

var page2 = new Page()
{
      Parent = page1,
      Title = "title2",
};

context.Pages.Add(page2);

Answer 2

Your mapping is correct and you don't need to remove the ChildSite property. You just can't use foreign key properties when you create your entities and "guess" how those autogenerated numbers will be after the entities are stored and use these numbers in the same entities which are being stored.

The following instead should work and create the expected rows in the database:

var parent1 = new Site
{
    Sitename = "Top 1",
    ChildSites = new List<Site>
        {
            new Site {Sitename = "Sub 1_5"},
            new Site {Sitename = "Sub 1_6"},
            new Site {Sitename = "Sub 1_7"},
            new Site {Sitename = "Sub 1_8"}
        }
};
var parent2 = new Site
{
    Sitename = "Top 2",
    ChildSites = new List<Site>
        {
            new Site {Sitename = "Sub 2_9"},
            new Site {Sitename = "Sub 2_10"},
            new Site {Sitename = "Sub 2_11"},
            new Site {Sitename = "Sub 2_12"}
        }
};
var parent3 = new Site
{
    Sitename = "Top 3",
    ChildSites = new List<Site>
        {
            new Site {Sitename = "Sub 3_13"},
            new Site {Sitename = "Sub 3_14"},
            new Site {Sitename = "Sub 3_15"},
            new Site {Sitename = "Sub 3_16"}
        }
};
var parent4 = new Site
{
    Sitename = "Top 4",
    ChildSites = new List<Site>
        {
            new Site {Sitename = "Sub 4_17"},
            new Site {Sitename = "Sub 4_18"},
            new Site {Sitename = "Sub 4_19"},
            new Site {Sitename = "Sub 4_20"}
        }
};
context.Sites.Add(parent1);
context.Sites.Add(parent2);
context.Sites.Add(parent3);
context.Sites.Add(parent4);
context.SaveChanges();

Edit

If the entities represented by a foreign key exists in the DB you can use it - for example:

var site = context.Sites.Single(s => s.Sitename == "Top 1");
site.ParentId = 4; // set a parent for "Top 1"
context.SaveChanges();

Add a new child:

var site = context.Sites.Single(s => s.Sitename == "Top 1");
site.ChildSites.Add(new Site { Sitename = "Sub 1_9" });
context.SaveChanges();

Remove a child:

var site = context.Sites.Single(s => s.Sitename == "Top 1");
// works because of lazy loading
var childToRemove = site.ChildSites.Single(s => s.Sitename == "Sub 1_9");
site.ChildSites.Remove(childToRemove);
context.SaveChanges();

etc.

Answer 3

Don't know what's the purpose of keepin a collection of ChildSite and one ChildSites for a site. Try out this configuration.

public class Site
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
     public virtual  Site Parent { get; set; }
    public string Sitename { get; set; }

    public virtual ICollection<Site> ChildSites { get; set; }
}

modelBuilder.Entity<Site>()
    .HasOptional(s => s.Parent)
    .WithMany(s => s.ChildSites)
    .HasForeignKey(s => s.ParentId);

Edit And when you are inserting you need to do like this.

 site s1=   new Site { ParentId = 0, Sitename = "Top 1" };
 site s2=   new Site { ParentId = 0, Sitename = "Top 2" };
 site s3=   new Site { ParentId = 0, Sitename = "Top 3" };
//....
 site s4=  new Site { Parent = s1, Sitename = "Sub 1_5"};

Answer 4

First of all - don't specify the id - I think the database is responsible for adding autonumbers to your entities.

Try fill your database this way instead:

var top1 = new Site { Sitename = "Top 1" };
var sub15 = new Site { ChildSite = top1 , Sitename = "Sub 1_5" };
var sub16 = new Site { ChildSite = top1 , Sitename = "Sub 1_6" };
var sub17 = new Site { ChildSite = top1 , Sitename = "Sub 1_7" };
...
context.Sites.Add(top1);

var top2 = new Site { Sitename = "Top 2" };
var sub29 = new Site { ChildSite = top2 , Sitename = "Sub 2_9" };
var sub210 = new Site { ChildSite = top2 , Sitename = "Sub 2_10" };
var sub211 = new Site { ChildSite = top2 , Sitename = "Sub 2_11" };
....
context.Sites.Add(top2);

....
context.SaveChanges();

Next: I think you should call your ChildSite for ParentSite that will make the code easyer to read:

var top1 = new Site { Sitename = "Top 1" };
var sub15 = new Site { ParentSite = top1 , Sitename = "Sub 1_5" };
var sub16 = new Site { ParentSite = top1 , Sitename = "Sub 1_6" };
var sub17 = new Site { ParentSite = top1 , Sitename = "Sub 1_7" };
...
context.Sites.Add(top1);

var top2 = new Site { Sitename = "Top 2" };
var sub29 = new Site { ParentSite = top2 , Sitename = "Sub 2_9" };
var sub210 = new Site { ParentSite = top2 , Sitename = "Sub 2_10" };
var sub211 = new Site { ParentSite = top2 , Sitename = "Sub 2_11" };
....
context.Sites.Add(top2);

....
context.SaveChanges();