EF Core Referencing same table, single collection

.net-core ef-code-first entity-framework-core

Question

Let's startof with the result I would like to have.

I have a member which can have multiple dance partners. Each dance couple is bound to a course.

So the situation of the models are:

Member:

  • Id
  • ...

Course:

  • Id
  • ...

MemberPair:

  • Id
  • CourseId
  • Member1Id
  • Member2Id

Entityframework core gives me the following solution:

public class MemberPair : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public Guid Member1Id { get; set; }

    [Required]
    public Guid Member2Id { get; set; }

    [Required]
    public Guid CourseId { get; set; }

    public virtual Member Member1 { get; set; }

    public virtual Member Member2 { get; set; }

    public virtual Course Course { get; set; }
}

and

public class Member : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    ...

    public virtual List<MemberPair> MemberPairs1 { get; set; }

    public virtual List<MemberPair> MemberPairs2 { get; set; }
}

But my ideal solution is:

public class MemberPair : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public Guid PartnerId { get; set; }       

    [Required]
    public Guid CourseId { get; set; }

    public virtual Member Partner{ get; set; }

    public virtual Course Course { get; set; }
}

and

public class Member : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    ...

    public virtual List<MemberPair> Partners { get; set; }
}

I know that the ideal solution is not valid. But is there another solution that is better? When I iterate all members I have to find out if I have to use MemberPairs1 or MemberPairs2 , and I would like to know if this can be made easier.

Thanks in advance.

1
1
8/26/2018 6:25:48 PM

Accepted Answer

Your comment triggered me to search for other solutions. So despite the fact that you've already accepted the answer, I wanted to explore some alternatives.


The problem with partner is that this is relative to the member. From member1's perspective member2 is the partner and vice versa.

The only way to implement this, more or less out-of-the-box, is to add redundant data. And by that I mean add the data from both perspectives.

For that we need to include Member in MemberPair:

public class MemberPair : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public Guid MemberId { get; set; }

    [Required]
    public Guid PartnerId { get; set; }       

    [Required]
    public Guid CourseId { get; set; }

    public Member Member { get; set; }

    public Member Partner { get; set; }

    public Course Course { get; set; }
}

Suppose you have:

Pair1     = { Id = 1, MemberId = 1, PartnerId = 2, CourseId = 1 }
Pair1Swap = { Id = 2, MemberId = 2, PartnerId = 1, CourseId = 1 }
Pair2     = { Id = 3, MemberId = 1, PartnerId = 3, CourseId = 2 }
Pair2Swap = { Id = 4, MemberId = 3, PartnerId = 1, CourseId = 2 }

Where ~swap is the redundant data. In that case

public class Member : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    // As the data is redundant only look at memberId. Check the fluent code.
    // MemberPairs should contain a list of pairs where 'this' is a member.
    // When (Member)Id == 1 then Pair1 + Pair2
    // When (Member)Id == 2 then Pair1Swap
    public ICollection<MemberPair> MemberPairs { get; set; }
}

You can then query:

member.MemberPairs.Select(p => p.Partner);

// result for Id==1: Partner with Id 2 and Partner with Id 3.
// result for Id==2: Partner with Id 1.

The drawback is that you'll have redundant data, which needs to be managed! But given this is just a team of two, I think this is quite easy to implement and an acceptable solution.


An alternative is to extend the Member object with an additional property (or method if you like), that generates a list of partners. In that case you don't need redundant data, nor an additional table. But you'll have to populate both memberPair collections.

public class Member : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public List<MemberPair> MemberPairs1 { get; set; }

    public List<MemberPair> MemberPairs2 { get; set; }

    public ICollection<Member> Partners
    { 
        get
        {
            // Get all pairs where 'this' is a member.
            var pairs = MemberPairs1.Union(MemberPairs2);
            // Get all partners by filtering by Id.
            return pairs.Select(p => p.MemberId1 == Id ? p.MemberId2 : p.MemberId1);
        }
    }
}

Original answer:

The problem is that you break the relation by saving it into one record. If you add an extra level 'Pair' and add 'PairMembers' to that, then it would look like this, assuming each course has different pairs:

Pair:
    Id
    CourseId

PairMember:
    Id
    PairId
    MemberId

Which will become something like this:

public class Pair : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public Guid CourseId { get; set; }

    public Course Course { get; set; }

    public ICollection<PairMember> PairMembers { get; set; }
}

public class PairMember : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required]
    public Guid PairId { get; set; }

    [Required]
    public Guid MemberId { get; set; }

    public Pair Pair { get; set; }

    public Member MemberId { get; set; }
}

public class Member : AuditableEntity, IEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public ICollection<PairMember> PairMembers { get; set; }
}

This will allow more than two partners in one pair, but that is something you can prevent in the front. While the backend allows multiple members, the presentation in the front is still member1 and member2.

I didn't test it, but I think this will get you the list of partners:

member.PairMembers
        .Select(p => p.Pair.PairMembers.Where(m => m.MemberId != member.Id))
1
8/27/2018 10:27:25 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow