Why is this column getting generated in EF Code-First migrations?

Question

I have a Mentorship entity, which has Student and Mentor as FKs:

    [Required]
    public int MentorId { get; set; }
    public virtual User Mentor { get; set; }

    [Required]
    public int StudentId { get; set; }
    public virtual User Student { get; set; }

User model:

    public virtual ICollection<Mentorship> Mentorships { get; set; }

Fluent API:

    modelBuilder.Entity<Mentorship>()
        .HasRequired(c => c.Mentor)
        .WithMany()
        .HasForeignKey(u => u.MentorId);

    modelBuilder.Entity<Mentorship>()
        .HasRequired(c => c.Student)
        .WithMany()
        .HasForeignKey(u => u.StudentId);

In my database, I see StudentId and MentorId columns which have been populated correctly, but I also see a User_UserId column that is not being used by anything. What have I done wrong?

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

You have used the WithMany() overload that configures the relationship to be required:many without a navigation property on the other side of the relationship - but you do have a navigation property on the other side of the relationship.

Try this:

modelBuilder.Entity<Mentorship>()
    .HasRequired(c => c.Mentor)
    .WithMany(d => d.Mentorships)
    .HasForeignKey(u => u.MentorId);

modelBuilder.Entity<Mentorship>()
    .HasRequired(c => c.Student)
    .WithMany(d => d.Mentorships)
    .HasForeignKey(u => u.StudentId);//oops! just realised that we just 
                                     //specified that Mentorships is using MentorId 
                                     //as the FK

References:

Required WithMany Method

Why do I get an extra foreign key?

Edit Scratch that. Just realised you are trying to create two relationships with only one navigation property on the many side. You can't have a navigation property with 2 foreign keys. You need to introduce inheritance on the User side or remove the Mentorships navigation property from the User class or introduce separate StudentMentorships and MentorMentorships navigation properties

Edit 2 Finding a user once you've defined separate navigation properties

int userId = 123;//the one we want to find
var user = Users.Where(x => x.StudentMentorships.Any(s => s.StudentID == userId) 
                         || x.MentorMentorships.Any(s => s.MentorID == userId);

edited May 23 '17 at 12:29

Community

1
1

answered Nov 06 '13 at 05:33

Colin

22,328
17
103
197

Using `WithMany()` without lambda parameter in one of the two mappings (maybe the second one) would work as well. It would mean that the `Mentorships` collection belongs to the first relationship only and the second relationship doesn't have an inverse collection. – Slauma Nov 06 '13 at 12:03
@Slauma yes. So `StudentMentorships` or `MentorMentorships` on its own. – Colin Nov 06 '13 at 13:06
Wow, didn't think of that. Thanks for the explanation. I would like for .Mentorships to list all Mentorships where that Student's PK is listed in either of a Mentorship's FKs. If you have time to demonstrate how this can be done, or any pointers, any help would be awesome. – RobVious Nov 06 '13 at 14:51
@RobVious not sure I understand exactly what you mean but I've added how to interrogate separate navigation properties to get a user that is in one or the other – Colin Nov 06 '13 at 15:43
Thanks Colin - I see the edit. This might be getting off-topic, but I'm interested in still using .Mentorships to access both because throughout my code, that's how I get them now - maybe I can define something in the Getter of that navigation property to do both? – RobVious Nov 06 '13 at 15:44
Yes, you could add a property like that. But then it is no longer a navigation property as such - so you can't use it in Linq-to-Entity queries. If you need it as a navigation property you may want to look at inheritance: http://weblogs.asp.net/manavi/archive/2010/12/24/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph.aspx but are you sure a Student is a User rather than Students having a UserProfile? No-one mentoring a student that isn't a user? – Colin Nov 06 '13 at 15:54
If I understand your question - a Student is a User, and a User has a List. Mentors are always Users as well. I decided not to use UserProfiles and just put everything user-specific directly on the User model (though I'm beginning to regret that...) – RobVious Nov 06 '13 at 16:23
More on whether inheritance is appropriate: http://stackoverflow.com/a/53354/150342 – Colin Nov 06 '13 at 16:57

Why is this column getting generated in EF Code-First migrations?

1 Answers1

Linked