how to annotate a parent-child relationship with Code-First

Question

When using the CTP 5 of Entity Framework code-first library (as announced here) I'm trying to create a class that maps to a very simple hierarchy table.

Here's the SQL that builds the table:

CREATE TABLE [dbo].[People]
(
 Id  uniqueidentifier not null primary key rowguidcol,
 Name  nvarchar(50) not null,
 Parent  uniqueidentifier null
)
ALTER TABLE [dbo].[People]
 ADD CONSTRAINT [ParentOfPerson] 
 FOREIGN KEY (Parent)
 REFERENCES People (Id)

Here's the code that I would hope to have automatically mapped back to that table:

class Person
{
    public Guid Id { get; set; }
    public String Name { get; set; }
    public virtual Person Parent { get; set; }
    public virtual ICollection<Person> Children { get; set; }
}

class FamilyContext : DbContext
{
    public DbSet<Person> People { get; set; }
}

I have the connectionstring setup in the app.config file as so:

<configuration>
  <connectionStrings>
    <add name="FamilyContext" connectionString="server=(local); database=CodeFirstTrial; trusted_connection=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

And finally I'm trying to use the class to add a parent and a child entity like this:

static void Main(string[] args)
{
    using (FamilyContext context = new FamilyContext())
    {
        var fred = new Person
        {
            Id = Guid.NewGuid(),
            Name = "Fred"
        };
        var pebbles = new Person
        {
            Id = Guid.NewGuid(),
            Name = "Pebbles",
            Parent = fred
        };
        context.People.Add(fred);
        var rowCount = context.SaveChanges();
        Console.WriteLine("rows added: {0}", rowCount);
        var population = from p in context.People select new { p.Name };
        foreach (var person in population)
            Console.WriteLine(person);
    }
}

There is clearly something missing here. The exception that I get is:

Invalid column name 'PersonId'.

I understand the value of convention over configuration, and my team and I are thrilled at the prospect of ditching the edmx / designer nightmare --- but there doesn't seem to be a clear document on what the convention is. (We just lucked into the notion of plural table names, for singular class names)

Some guidance on how to make this very simple example fall into place would be appreciated.

UPDATE: Changing the column name in the People table from Parent to PersonId allows the Add of fred to proceed. Howerver you'll notice that pebbles is a added to the Children collection of fred and so I would have expected pebbles to be added to the database as well when Fred was added, but such was not the case. This is very simple model, so I'm more than a bit discouraged that there should be this much guess work involved in getting a couple rows into a database.

Answer 1

You need to drop down to fluent API to achieve your desired schema (Data annotations wouldn't do it). Precisely you have an Independent One-to-Many Self Reference Association that also has a custom name for the foreign key column (People.Parent). Here is how it supposed to get done with EF Code First:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
                .HasOptional(p => p.Parent)
                .WithMany(p => p.Children)
                .IsIndependent()
                .Map(m => m.MapKey(p => p.Id, "ParentID"));
}

However, this throws an InvalidOperationException with this message Sequence contains more than one matching element. which sounds to be a CTP5 bug as per the link Steven mentioned in his answer.

You can use a workaround until this bug get fixed in the RTM and that is to accept the default name for the FK column which is PersonID. For this you need to change your schema a little bit:

CREATE TABLE [dbo].[People]
(
     Id  uniqueidentifier not null primary key rowguidcol,
     Name  nvarchar(50) not null,
     PersonId  uniqueidentifier null
)
ALTER TABLE [dbo].[People] ADD CONSTRAINT [ParentOfPerson] 
FOREIGN KEY (PersonId) REFERENCES People (Id)
GO
ALTER TABLE [dbo].[People] CHECK CONSTRAINT [ParentOfPerson]
GO

And then using this fluent API will match your data model to the DB Schema:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
                .HasOptional(p => p.Parent)
                .WithMany(p => p.Children)
                .IsIndependent();
}

Add a new Parent record containing a Child:

using (FamilyContext context = new FamilyContext())
{
    var pebbles = new Person
    {
        Id = Guid.NewGuid(),
        Name = "Pebbles",                    
    };
    var fred = new Person
    {
        Id = Guid.NewGuid(),
        Name = "Fred",
        Children = new List<Person>() 
        { 
            pebbles
        }
    };                
    context.People.Add(fred);               
    context.SaveChanges();                                
}

Add a new Child record containing a Parent:

using (FamilyContext context = new FamilyContext())
{
    var fred = new Person
    {
        Id = Guid.NewGuid(),
        Name = "Fred",                
    };
    var pebbles = new Person
    {
        Id = Guid.NewGuid(),
        Name = "Pebbles",
        Parent = fred
    };
    context.People.Add(pebbles);
    var rowCount = context.SaveChanges();                                
}

Both codes has the same effect and that is adding a new parent (Fred) with a child (Pebbles).