CODEWITHSUNDEEP
entity-frameworkef-code-first
trevorc
trevorc

Reputation: 3031

What is the syntax for self referencing foreign keys in EF Code First?

I am trying to reference a foreign key from SpouseId to Id in the Contact table. What is the syntax for doing this? I can't seem to find an example. Thanks.

I have a class like this:

public class Contact
{
    public int Id {get;set;}
    public string Name {get;set;}
    public int? SpouseId {get;set;}
}

EDIT1 Per the link provided by Joel Cunningham and the answer from Morteza I have added some additional code.

ContactMap.cs

public partial class ContactMap : EntityTypeConfiguration<Contact>
{
  public ContactMap()
     {
       this.ToTable("Contact");
       this.HasKey(c => c.Id);
       this.HasOptional(c => c.Spouse)
           .WithMany()
           .IsIndependent()
           .Map(m => m.MapKey(fk => fk.Id, "SpouseId"));
     }
}

MyObjectContext.cs

public class MyObjectContext : DbContext, IDbContext
{
  public DbSet<Contact> Contacts {get;set;}
  protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Configurations.Add(new ContactMap());
     }
}

Note: I also added the "[ForeignKey("SpouseId")]" attribute to my Spouse property in my Contact class. Unfortunately I keep getting "Sequence contains more than one matching element".

EDIT2: Morteza's answers below is correct. To summarize: For self referencing foreign keys you can either mark the property as a "[ForeginKey("SpouseId")] OR use the fluent API example below. The errors I reported in some of my comments were caused by my unit test. EF generated the db the correct way. I found a good link where Craig Stuntz outlined why auto-increment keys and self-referencing foreign keys can cause the "Unable to determine a valid ordering for dependent operations" error. I believe this is what my problem is. Hope this helps someone.

Upvotes: 48

Views: 52436

Answers (4)

CodeByAk
CodeByAk

Reputation: 227

_ = builder.HasMany(e => e.Children)
            .WithOne(e => e.Parent)
            .HasForeignKey(e => e.ParentId);

Above code worked for me

Upvotes: 0

Ali Bah
Ali Bah

Reputation: 151

[Table("Move")]
public class Move
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long ID { get; set; }
    public long? ParentID { get; set; }

    [InverseProperty("Children")]
    public virtual Move Parent { get; set; }
    public virtual ICollection<Move> Children { get; set; }
}

Upvotes: 9

Rick R
Rick R

Reputation: 256

Also, the navigation property Spouse should be virtual to avoid unnecessary JOIN queries:

 public virtual Contact Spouse { get; set; }

Upvotes: 4

Morteza Manavi
Morteza Manavi

Reputation: 33206

Something like this will work:

public class Contact
{
    public int Id {get;set;}
    public string Name {get;set;}
    public int? SpouseId {get;set;}

    [ForeignKey("SpouseId")]
    public Contact Spouse {get;set;}
}

ForeignKeyAttribute has been added to System.ComponentModel.DataAnnotations by CTP5 assembly.

Update I: CTP5 Bug:

Due to a bug in CTP5, creating an Independent Self Referencing Associations throws an exception. The workaround is to use Foreign Key Associations instead (which is always recommended regardless).

Update II: Using Fluent API to Configure a Self Referencing Association:

You can also use fluent API to achieve this, if you prefer:

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? SpouseId { get; set; }                

    public Contact Spouse { get; set; }
}

public class Ctp5Context : DbContext
{
    public DbSet<Contact> Contacts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Contact>()
                    .HasOptional(c => c.Spouse)
                    .WithMany()
                    .HasForeignKey(c => c.SpouseId);
    }
}

Working with the Model:

using (var context = new Ctp5Context())
{
    Contact contact = new Contact()
    {
        Name = "Morteza",
        Spouse = new Contact()
        {
            Name = "Code-First"
        }
    };
    context.Contacts.Add(contact);
    context.SaveChanges();
}

Upvotes: 72

Related Questions