Several one to many relationships between two tables Entity Framework 6

Question

I have Guest Entity

public class Guest
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
    }

I need an Entity that describes relationshipType between two guests. I came up with

public class RelationshipGuestLink
    {
        public Guid Id { get; set; }
        public Relationship Relationship { get; set; }
        public Guid RelationshipId { get; set; }
        public Guest FirstGuest { get; set; }
        public Guid FirstGuestId { get; set; }
        public Guest SecondGuest { get; set; }
        public Guid SecondGuestId { get; set; }
        public ProfileRelationshipType RelationshipType { get; set; }
        public Guid RelationshipTypeId { get; set; }
    }

And map them using fluentApi

public RelationshipGuestLinkConfiguration()
    {
        ToTable("MyTable");
        HasKey(x => x.Id);
        HasOptional(x => x.Relationship).WithMany().HasForeignKey(x => x.RelationshipId);
        HasOptional(x => x.FirstGuest).WithMany().HasForeignKey(x => x.FirstGuestId);
        HasOptional(x => x.SecondGuest).WithMany().HasForeignKey(x => x.SecondGuestId);
        HasOptional(x => x.RelationshipType).WithMany().HasForeignKey(x => x.RelationshipTypeId);
    }

Problem is, I don't know what to do with WithMany() part. Do I have to create two ICollection<RelationshipGuestLink> in Guest class? Or can I map them to one collection?

In the end I need to create something like family, and be able to access family members from any member of a family.

EDIT: Thanks to Vidmantas Blazevicius for help. I moved RelationshipType to Relationship class, as it belong there. And without it felt empty and useless. Also renamed my First and Second guests to something more appropriate.

My problem is with my mania to write down every link in models with fluentApi from two sides of the problem. I see those links as Foreign keys, and as my RelationshipGuestLink table has two keys to Guest table, I tried to create two links in EF.

But I still think one link will only get me the relatives of the first Guest. And to get his whole family I'll have to write additional code to walk this tree and create a List<Guest>

Answer 1

I think I understand what you are trying to accomplish and a single ICollection<RelationshipGuestLink> should be sufficient. By doing this you achieve a 1:M with your joining table RelationshipGuestLink.

    public class Guest
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public string Surname { get; set; }

        public virtual ICollection<RelationshipGuestLink> RelationshipLinks { get; set; }
    }

So if the example is a Child, Father and Mother - all being instances of the Guest class - you would have two relationship links for Child (to the father and the mother), one for each parent (relating to each other).

EDIT:

Below is how I imagine your model builder looking for RelationshipGuestLink. It should be as simple as that. There is M:1 relationship from this table to the Guest table in terms of foreign key GuestId. Same for RelatedGuest. Your Relationship property is really only going to be on one end. Whilst it also M:1 relationship with the Relationship table - you don't really want to have ICollection<RelationshipGuestLink> on your Relationship` entity class because, but you do want that the foreign key exists. Whether you want to cascade on delete (which is highly advisable in most cases) - it is up to you.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RelationshipGuestLink>()
            .HasKey(x => x.Id);

        modelBuilder.Entity<RelationshipGuestLink>()
                .HasRequired(x => x.Guest).WithMany(x => x.RelationshipLinks).HasForeignKey(x => x.Guest);

        modelBuilder.Entity<RelationshipGuestLink>()
            .HasRequired(x => x.Relationship).WithRequiredDependent();

    }

    public class RelationshipGuestLink
    {
        public Guid Id { get; set; }
        public Guid GuestId { get; set; }
        public Guid RelationshipId { get; set; }
        public Guid RelatedGuestId { get; set; }

        public Guest Guest { get; set; }
        public Relationship Relationship { get; set; }
        public Guest RelatedGuest { get; set; }

    }