none
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.

    Thursday, December 16, 2010 2:43 PM

All replies

  • ...try what I've suggested here a few days ago...
    http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/30c4fa2a-ae2a-4494-908e-f6c1f7cbc32f/#bbfc7eb4-c035-4c34-bde5-f217bb541828

    ...it's pretty much the same
    you have to explicitly specify the parent relation 

    modelBuilder.Entity<Person>().HasOptional(n => n.Parent).WithMany().IsIndependent();
    ...that's w/o children
    or you can try including the children in the 'withmany' (e.g. WithMany(p=>p.Children)
    hope it helps,
    Gaga


    Thursday, December 16, 2010 3:55 PM
  • That still results in an exception "Invalid column name 'PersonId'

    Where is this ModelBuilder documented?

    Thursday, December 16, 2010 4:20 PM
  • ...modelBuilder goes into your FamilyContext...

     

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

    ...and that works
    just add the
    context.People.Add(pebbles);
    as well
    you can repost the full repro w/ changes to see what's going on

    P.S. documentation is pretty scarce, mostly the walkthrus, here...
    P.P.S.
    also add the initializer - it might be that your db is holding to the old structure,
    DbDatabase.SetInitializer<FamilyContext>(
    new DropCreateDatabaseIfModelChanges<FamilyContext>());
    ...or even better for start, go the sql manager and drop the db by hand

    Thursday, December 16, 2010 6:17 PM
  • I'm sorry there is still the issue of "Invalid column name PersonId".  Is there something I'm missing in my Person class.  Perhaps I must include a property to hold the nullable foreign key, of the possible Parent, as well as the Parent instance.  (that seems unfortunate, but manageable --- my gut tells me i'm going to have to do that anyway to play well with WCF RIA services)
    Thursday, December 16, 2010 6:49 PM
  • ...read my previous post (edited parts),
    drop the Db and restart - I think that's it'
    if it doesn't work - copy paste full program.cs here that you tried (connection is irrelevant)
    and I'll try and see what's the issue run it here actually

    Best

    Gaga

    Thursday, December 16, 2010 6:52 PM
  • R Shillington,

     

    The problem here is that the entity is mapping to an appropriate structure in the database but using different names than you have.  I assume you cannot change the database schema—if you can, then renaming the Parent column to PersonId should result in correct mappings for the relationship.  If you need to keep the column called “Parent” then the correct way to specify the mapping so that “Parent” is used as the FK in the database would be this:

     

        modelBuilder.Entity<Person>().HasOptional(p => p.Parent).WithMany(p => p.Children).IsIndependent().Map(m => m.MapKey(p => p.Id, "Parent"));

     

    However, there is a bug in CTP5 that prevents this from working.  A usual workaround for this bug is to include the foreign key in your entity.  This is complicated a bit in your situation because the foreign key property would have the same name (“Parent”) as your navigation property.  This means that the FK in your entity will have to have a different name to the FK column in your database.  You can do this in two ways.  First, with data annotations:

     

        class Person

        {

            public Guid Id { get; set; }

            public String Name { get; set; }

            [Column(Name = "Parent")]

            public Guid? ParentId { get; set; }

            [ForeignKey("ParentId")]

            public virtual Person Parent { get; set; }

            public virtual ICollection<Person> Children { get; set; }

        }

     

    Or using the fluent API in OnModelCreating:

     

        modelBuilder.Entity<Person>().HasOptional(p => p.Parent).WithMany(p => p.Children).HasForeignKey(p => p.ParentId);

        modelBuilder.Entity<Person>().Property(p => p.ParentId).HasColumnName("Parent");

     

    With regard to the ModelBuilder documentation, the best bet for this preview release are these two blog posts:

     

    http://blogs.msdn.com/b/adonet/archive/2010/12/14/ef-feature-ctp5-fluent-api-samples.aspx

    http://blogs.msdn.com/b/adonet/archive/2010/12/10/code-first-mapping-changes-in-ctp5.aspx

     

    Hope this helps.

     

    Thanks,

    Arthur

    Thursday, December 16, 2010 6:54 PM