how to use Code-First to annotate a parent-child connection

code-first entity-framework

Question

I'm attempting to develop a class that maps to a very basic hierarchy table using the Entity Framework CTP 5 code-first library (as announced by here).

Here is the SQL code for creating 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)

I would want to have the following code 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; }
}

The connectionstring is set up as follows in my app.config file:

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

Finally, I'm attempting to create a parent entity and a child entity using the class as follows:

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);
    }
}

Clearly, something is lacking from this. The one I encounter is:

Invalid column name 'PersonId'.

Although my team and I are excited about the idea of doing away with the edmx / designer nightmare, I am aware of the significance of convention over configuration. However, there doesn't seem to be a clear document on what the convention is. (We accidentally discovered the idea of multiple table names for single class names.)

I would appreciate any direction on how to make this very basic example work.

UPDATE: Renaming the Parent column in the People database to PersonId enables the Add offred to continue. But you'll see thatpebbles is added to the collection of children'sfred thus when Fred was added to the database, I would have anticipated that pebbles would also be included, but that was not the case. I'm more than a little frustrated that there should be this much guesswork involved in inserting a few rows into a database when this is a really basic model.

1
17
12/16/2010 6:10:25 PM

Accepted Answer

To accomplish your desired schema, you must switch to fluent API (data annotations won't work). You specifically have anIndependent One-to-Many Self Reference Association This further uses a unique name for the foreign key column (People.Parent). Here is how using EF Code First should be done:

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"));
}

Nevertheless, this raises anInvalidOperationException containing the signal Sequence has many elements that match it.. This, according to the URL Steven supplied in his response, seems to be a CTP5 problem.

As a workaround until this error is rectified in the RTM, you may accept the FK column's default name, which isPersonID . To do this, you must slightly alter your schema:

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

A fluent API will then be used to 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();
}

Create a new Parent record with a Child in it:

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();                                
}

Create a brand-new Child record with 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 programs create a new parent (Fred) and kid, which is the same result (Pebbles).

15
4/14/2013 10:30:19 AM

Popular Answer

This is how Entity Framework 6 allows you to accomplish it, take noticepublic Guid? ParentId { get; set; } For it to function, the foreign key MUST be nullable.

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

https://stackoverflow.com/a/5668835/3850405



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow