EF is creating 2 foreign keys for navigation property

Question

The following entity configuration it creating a double foreign key for one of the navigation properties:

public User  : IdentityUser
{
     //public string Id { get; set; } //on base class

     public virtual ICollection<Earning> Earnings { get; set; }
}


public class Earning
{

    public int EarningId { get; set; }

    public virtual User User { get; set; }
    public string UserId { get; set; }

    public virtual User Sender { get; set; }
    public string SenderId { get; set; }


}

public class EarningConfiguraiton : EntityTypeConfiguration<Earning>
{
    public EarningConfiguraiton() 
    {
        this.HasRequired(e => e.User)
            .WithMany(u => u.Earnings)
            .HasForeignKey(e => e.UserId);

        this.HasRequired(e => e.Sender)
            .WithMany()
            .HasForeignKey(e => e.SenderId)
            .WillCascadeOnDelete(false);
    }
}

However it generates 3 foreign keys for 2 navigation properties. "User_Id" is seemingly redundant. Should I remove , if so how?

   CreateTable(
            "dbo.Earnings",
            c => new
                {
                    EarningId = c.Int(nullable: false, identity: true),
                    UserId = c.String(maxLength: 128),
                    SenderId = c.String(maxLength: 128),                      
                    User_Id = c.String(maxLength: 128),
                })
            .PrimaryKey(t => t.EarningId)
            .ForeignKey("dbo.AspNetUsers", t => t.SenderId)
            .ForeignKey("dbo.AspNetUsers", t => t.UserId)
            .ForeignKey("dbo.AspNetUsers", t => t.User_Id)
            .Index(t => t.UserId)
            .Index(t => t.SenderId)
            .Index(t => t.User_Id);

Further more context.Users.Include(u => u.Earnings) only works with User_Id column populated, rather than UserId.

I even get this migration generated on a brand new database

do you add a `EarningConfiguraiton` to the Configuration collection in the `OnModelCreating` of your context ? — tschmit007, Nov 15 '14 at 15:21
EF automatically registers classes of type EntityTypeConfiguration<> in the assembly so there is no need to add anything. Changes to this configuration get picked up so I know that's true (how else would it know to generate the SenderId FK above. — parliament, Nov 15 '14 at 17:18

score 1 · Accepted Answer · answered Nov 19 '14 at 06:16

1

There is actually a long outstanding work item on this issue with the Fluent API not correctly handling inverse navigation properties. It has been considered to be a low priority issue, and was not included in the 6.1 release due to Triage. http://entityframework.codeplex.com/workitem/1135

answered Nov 19 '14 at 06:16

Claies

22,124
4
53
77

I see, I guess I'm not going crazy. I'm surprised I haven't ran into this before after so long using EF navigation properties. – parliament Nov 20 '14 at 01:56

score 0 · Answer 2 · edited May 23 '17 at 12:15

Ok I'm able to get past this but the bounty is still available for a better way.

Gert's comments in this answer helped me put it together.

Basically since there are 2 User entities on the Earning, EF is not going to assume which of them is bi-directional for the Earnings collection on the User.

Using [InversePropoperty] attribute works but I'm not able to configure this using Fluent Api:

Solution:

 public User  : IdentityUser
 {  
     [InverseProperty("User")]
     public virtual ICollection<Earning> Earnings { get; set; }
 }


 public class Earning
 {
     [InverseProperty("Earnings")]
     public virtual User User { get; set; }

     public virtual User Sender { get; set; }
     public string SenderId { get; set; }
  }

Now it generates one less foreign key. However I'm unable to configure this using Fluent Api.

public class EarningConfiguraiton : EntityTypeConfiguration<Earning>
{
    public EarningConfiguraiton() 
    {
        this.HasRequired(e => e.User)
            .WithMany(u => u.EmailEarnings)
            .HasForeignKey(e => e.UserId) //doesnt honor this. creates User_Id anyways
            .WillCascadeOnDelete(false);

    }
}

I don't know if it's a bug but bounty goes to whoever can figure it out. I'm using EF 6.1.1

EF is creating 2 foreign keys for navigation property

2 Answers2