Blazor Helpers App Members: 5. Many to Many Entity Relationships


With Code-First, to get Entity Framework to generate the scaffolding for when one entity requires a list of another, both entities require a list of the other as a property. When the new migration is added a secondary table that joins both is generated. This simplifies some of the explicit coding for group membership covered in some of the previous posts in this series.

Entity Framework Core 5 supports implicit Many to Many relationships between entities. That is, one entity can simultaneously have a list of another entity as a property and vice versa without any further code-first coding. EF generates a secondary table that is combination of both entities Ids that performs a join between the two. As such it provides a join between the two entities that previously had to be explicitly coded for as a separate joining entity class.

This article discusses this in details: Many-to-many Relationship

At a SQL Server level, the table is a list of pairs of Ids from the two entities that are co-joined. Where one table has an instance of the other entity in its list, the join table has the primary keys of both entities as one record. Where one entity has a number of instances of the second entity in its list, SQL has a record for each pair.

Consider the Member and Group classes:

public class Member
{   [key]
    public int Id {get; set;}
    ...
    public List<MemberGroup> Memberof {get; set;}
}
public class Group
{   [key]
    public int Id {get; set;}
    ...
    public List<Member> Members {get; set;}
}
  • A member can be a member of multiple groups.
  • A group can have multiple members

The EF Migration Builder generates the following code to create the join table:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "MemberMemberGroup",
                columns: table => new
                {
                    MemberOfId = table.Column<int>(type: "int", nullable: false),
                    MembersId = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    ...
                });

            migrationBuilder.CreateIndex(
                name: "IX_MemberMemberGroup_MembersId",
                table: "MemberMemberGroup",
                column: "MembersId");
        }

When the code is run and a member has two groups added, the following 2 records are generated in SQL:

MemberOfId MembersId
2 225
3 225

In the above case, member number 255 is a member of groups number 2 and 3 (All and Athlete groups).

In this app this information is used a follows:

  • At times you may need to know what groups a member is member of, for example when giving access to certain resources. Are they a member of the Committee group?
    • Get a list of members and their groups
  • Other times you might want to get a list of members in a group to send an email to.
    • Get a list of all groups and their members.

When so doing you need to add some .Includes when getting a list of an entity from its DBContext. This has been discussed in previous blogs here. Another reference is: Include Multiple Levels

    var list = await _context.Members.Include(Member => Member.MemberOf).ToListAsync();

This will get a list of Members and the MemberOf property of each member will be populated with its list of groups. Without the .Include that property will show as being null.

    var list = await _context.Groups.Include(Group => Group.Members).ToListAsync();

This will get a list of Groups and the Members property of each group will be populated with its list of members. Without the .Include that property will show as being null.

Conclusion

We now have a more elegant and robust coding mechanism for have a list of one entity as a a property of another than manually serialising a list Ids of the of the entities in the list and storing that string in the primary entity.

 TopicSubtopic
   
 This Category Links 
Category:Blazor Index:Blazor
  Next: > Blazor Helpers App Members
<  Prev:   Blazor Helpers App Members