Semi-recursive associations with Entity Framework Code First

Question

I'm hoping you can help me with a small issue I'm having.

I have an entity framework Code First setup where I have the following setup:

public class UserDetails{
   public int Id {get;set;}
   //... some other properties .. //

   //This represents the approval group the user is a member of.
   public virtual ApprovalGroup {get;set;}

   //This represents the approval groups that the user is resposible for approving
   public virual ICollection<ApprovalGroup> ApprovalGroups {get;set;}
}

public class ApprovalGroup
{
    public int Id {get;set;}
    public string Name {get;set;}

    public virtual UserDetails Approvee {get;set;}
    public virtual ICollection<UserDetails> Members {get;set;}
}

In my db context I have the following:

modelBuilder.Entity<ApprovalGroup>().ToTable("ApprovalGroup")
   .HasKey(t=>t.Id)
   .HasRequired(t=>t.Approver);

modelBuilder.Entity<ApprovalGroup>().HasMany(t=>t.Members)
    .WithOptional().WillCascadeOnDelete(false);

So an approval group MUST have an approvee set-up, however it could potentially have no members (particularly when first configured).

This code is running, however when I examine the database that it creates the approval group table has an extra column in it that is called "UserDetails_Id". It is set up as a foreign key, but it is always null.

The schema of the table it creates has the following columns:

Id, Name, UserDetails_Id, Approver_Id

I have no idea why it is creating the unnecessary table "UserDetails_Id" and I'd like it not to as there is no reason for it. I suspect I have something wrong with my configuration/mapping behaviour but I can't figure out what it is.

As of yet, google has failed to shed light on what I'm doing wrong so if anyone here can help it would be greatly appreciated.

Nik

Answer 1

The issue you are experiencing is because of the improper/incomplete relationships mappings, combined with EF default conventions.

I would suggest you always configuring the relationships separately, only once per relationship and use the overloads that match exactly the presence/absence of the navigation and explicit FK properties.

In this particular case you have two one-to-many bidirectional (i.e. with navigation properties on both ends) relationships with no explicit FK properties. So the correct configuration should be like this:

// Entities
modelBuilder.Entity<ApprovalGroup>()
   .ToTable("ApprovalGroup")
   .HasKey(t => t.Id);

modelBuilder.Entity<UserDetails>()
   .ToTable("UserDetails")
   .HasKey(t => t.Id);

// Relationships
modelBuilder.Entity<ApprovalGroup>()
   .HasRequired(t => t.Approver)
   .WithMany(t => t.ApprovalGroups);

modelBuilder.Entity<ApprovalGroup>()
   .HasMany(t => t.Members)
   .WithOptional(t => t.ApprovalGroup) // or whatever the name of the navigation property is (it's missing in the posted code)
   .WillCascadeOnDelete(false);