EF 4.1 Code First Relationship table

Question

Setup

Using MVC 3 + Code First

Here are my classes

public class Member
    {
        [Key]
        public Guid ID { get; set; }

        [Required]
        public String Email { get; set; }

        [Required]
        public String FirstName { get; set; }

        [Required]
        public String LastName { get; set; }

        public String Sex { get; set; }

        public String Password { get; set; }

        public String PasswordSalt { get; set; }

        public DateTime RegisterDate { get; set; }

        public DateTime LastOnline { get; set; }

        public String SecurityQuestion { get; set; }

        public String SecurityAnswer { get; set; }

        public virtual ICollection<FamilyMember> Families { get; set; }

        public virtual ICollection<Relationship> Relationships { get; set; }


    }


public class Relationship
    {
        [Key]
        public Guid ID { get; set; }

        [ForeignKey("Member1")]
        public Guid Member1ID { get; set; }

        [ForeignKey("Member2")]
        public Guid Member2ID { get; set; }

        public Guid RelationshipTypeID { get; set; }

        public virtual RelationshipType RelationshipType { get; set; }

        public virtual Member Member1 { get; set; }

        public virtual Member Member2 { get; set; }

    }

Here is the problem

The database table "Relationship" is being created with the following columns:

ID, Member1ID, Member2ID, RelationshipTypeID, Member_ID

Why is it creating the Member_ID column?

I've seen this post in which the user has the same type of setup, but I am unsure of how to define the InverseProperty correctly. I tried using fluent API calls but from what I can tell they will not work here since I have two foreign keys referring to the same table.

Any help would be appreciated!

Answer 1

Member_ID is the foreign key column which EF created for the navigation property Member.Relationships. It belongs to a third association from Member.Relationships refering to an end endpoint which is not exposed in your Relationship entity. This relationship has nothing to do with the other two relationships from Relationship.Member1 and Relationship.Member2 which also both have an endpoint not exposed in Member.

I guess, this is not what you want. You need always pairs of endpoints in two entities to create an association. One endpoint is always a navigation property. The second endpoint can also be a navigation property but it is not required, you can omit the second navigation property.

Now, what is not possible, is to associate two navigation properties (Member1 and Member2) in one entity with one navigation property (Relationships) in the other entity. That is what you are trying to do apparently.

I assume that your Member.Relationships property is supposed to express that the member is either Member1 or Member2 in the relationship, or that it participates in the relationship, no matter if as Member1 or Member2.

Unfortunately you cannot express this in the model appropriately. You have to introduce something like RelationsshipsAsMember1 and RelationsshipsAsMember2 and for these two collection you can use the InverseProperty attribute as shown in the other question. In addition you can add a helper property which concats the two collections. But this is not a mapped property but readonly:

public class Member
{
    // ...

    [InverseProperty("Member1")]
    public virtual ICollection<Relationship> RelationshipsAsMember1 { get; set; }
    [InverseProperty("Member2")]
    public virtual ICollection<Relationship> RelationshipsAsMember2 { get; set; }

    public IEnumerable<Relationship> AllRelationships
    {
        get { return RelationshipsAsMember1.Concat(RelationshipsAsMember2); }
    }
}

Accessing AllRelationships will cause two queries and roundtrips to the database (with lazy loading) to load both collections first before they get concatenated in memory.

With this mapping the Member_ID column will disappear and you will only get the two expected foreign key columns Member1ID, Member2ID because now you have only two associations and not three anymore.

You could also think about if you need the Relationships collection in the Member entity at all. As said, navigation properties on both sides are not required. If you rarely need to navigate from a member to its relationships you could fetch the relationships also with queries on the Relationship set, like so:

var relationships = context.Relationships
    .Where(r => r.Member1ID == givenMemberID || r.Member2ID == givenMemberID)
    .ToList();

...or...

var relationships = context.Relationships
        .Where(r => r.Member1ID == givenMemberID)
    .Concat(context.Relationships
        .Where(r => r.Member2ID == givenMemberID)
    .ToList();

This would give you all relationships the member with ID = givenMemberID participates in without the need of a navigation collection on the Member entity.