Many-to-Many Relationships with Additional Fields

This tutorial explains how to implement Many-to-Many relationships with additional columns in entity framework code first. If you want to learn basics of Many-to-Many relationships in entity framework please refer to this blog post.

Consider this M:M relationship example: a student can enroll MANY courses, and a course can have MANY students enrolled. It is relatively straightforward to implement this relationship in entity framework (as explained in this blog post). If we need to record the date of the enrollments, then the way we implement this relationship will significantly change. To record the enrollment dates, we will need to add a new column to the Linking Table. Adding an additional column to the Linking Table requires major changes in the configuration of the M:M relationship.

Linking Table

Configuring Many-To-Many (M:M) Relationships with Additional Fields

In these M:M scenarios where additional field is needed, we will consider this M:M relationship as a combination of two 1:M relationships.

For example, consider this example: A football player can be associated with MANY football teams (current or past), and a football team can have MANY players (current or past). In this relationship, we need to record additional information about this transfer such as the starting year and ending year of the contract, and the price of the transfer. Let’s call our linking table as Transfers.

We could name the linking table as TeamPlayers by combining the names of the other two entities. However, we preferred to give a unique name, Transfers, to the linking table independent from the other two entities. This is because now we treat Transfers table not only as a linking table but also as a table that will represent Transfer entities. Therefore, we will create the Transfer class in the domain model.

Based on the figure above, now we have three separate entities, and two 1:M relationships among these entities:

• A transfer can involve ONLY and ONLY ONE player; a player can be involved in MANY transfers (at different time points),
• A transfer can involve ONLY and ONLY ONE team; a team can perform MANY transfers.

Therefore, in a many-to-many relationship if there are additional fields (or information) that defines the relationship, these fields are actually attached to the linking table, converting the linking table into an Entity table to which an entity from the domain model can be mapped. In our case, we named the linking table as Transfers table which will store the Transfer entities.

Working on an Example

Configuring Many-to-Many Relationships with additional fields is actually the same as configuring two separate 1-to-Many relationship: 1) Transfer – Team, 2) Transfer – Player.

Please note that the linking entity, Transfer, will be always the child (or dependent) entity in two separate 1-M relationship it participates in. Therefore, foreign key properties should be declared in Transfer entity class. The following code declares the Transfer class:

public class Transfer
{
    public int TransferId { get; set; }
 
    public int StartYear { get; set; }
    public int EndYear { get; set; }
    public Double Price { get; set; }

    public Team Team { get; set; }
    public int TeamIdFK { get; set; }

    public Player Player { get; set; }
    public int PlayerIdFK { get; set; }
}

You may notice that there are two Navigation properties defined: Team and Player. This is because each transfer instance is identified by a certain team and player (or each transfer involves only one team and only one player). Therefore, we have two foreign key properties, TeamIdFK and PlayerIdFK, pointing to the corresponding navigation property.

Let’s declare the Team class:

public class Team
{
    public int TeamId { get; set; }

    public string TeamName { get; set; }

    public int YearEstablished { get; set; }

    public List<Transfer> Transfers { get; set; }
}

The Team entity has a List type navigation property, Transfers, that indicates a team entity can be associated with multiple instances of transfers. With the help of this navigation property, entity framework code first will be able to infer that every team can do have many transfers.

Similarly, Player class will have a List type navigation property, Transfers, which implies that a player can be involved in many transfers. Here is the declaration of the Player class:

public class Player
{
    public int PlayerId { get; set; }

    public string FullName { get; set; }

    public int BirthYear { get; set; }

    public int LisenceYear { get; set; }

    public List<Transfer> Transfers { get; set; }
}

To finish the configuration of these relationship, we will need to have the following fluent api:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Player>()
                .HasMany(p => p.Transfers)
                .WithRequired(t => t.Player)
                .HasForeignKey(t => t.PlayerIdFK);

    modelBuilder.Entity<Team>()
                .HasMany(te => te.Transfers)
                .WithRequired(tr => tr.Team)
                .HasForeignKey(tr => tr.TeamIdFK);
}

If you have trouble understanding the code above please review this blog post.

Using Package Manager Console, please execute the following commands “add-migration MM1” and “update-database” to map our model to the database.

When you browse and open the database, you will see that there are 3 tables in the database representing its corresponding entity:

• Transfers: The table to which Transfer entity is mapped,
• Teams: The table to which Team entity is mapped,
• Players: The table to which Player entity is mapped.

Our database is ready. Now we can create a new web form for managing transfers.

ManageTransfers.aspx Web Form

Please create a new web form called ManageTransfers.aspx in your application. It should have an interface similar to the following:

If you are not familiar with managing data in One-To-Many relationships please check this tutorial.

Creating a new transfer (Connected Approach)

The following code adds a new transfer instance to the database. Please note that the foreign key values are grabbed from the corresponding drop down lists.

protected void btn_CreateCourse_Click(object sender, EventArgs e)
{
    using (var db = new FootballDbContext())
    {
        Transfer transfer = new Transfer()
        {
             Price = Convert.ToInt32(txt_NewCost.Text),
             StartYear = Convert.ToInt32(txt_NewEndYear.Text),
             EndYear = Convert.ToInt32(txt_NewStartYear.Text),
             PlayerIdFK = Convert.ToInt32(drp_NewPlayerInvolved.SelectedValue),
             TeamIdFK = Convert.ToInt32(drp_NewTeamInvolved.SelectedValue)
        };

        db.Transfers.Add(transfer);

        db.SaveChanges();
    }
}

Update/delete a transfer

protected void btn_Update_Click(object sender, EventArgs e)
{
     using (var db = new FootballDbContext())
     {
         int transferid = Convert.ToInt32(grd_Transfers.SelectedValue);

         Transfer transfer = db.Transfers.Find(transferid);

         transfer.Price = Convert.ToDecimal(txt_UpdateCost.Text);
         transfer.StartYear = Convert.ToInt32(txt_UpdateEndYear.Text);
         transfer.EndYear = Convert.ToInt32(txt_UpdateStartYear.Text);

         db.SaveChanges();
     }
}
protected void btn_Delete_Click(object sender, EventArgs e)
{
    using (var db = new FootballDbContext())
    {
        int transferid = Convert.ToInt32(grd_Transfers.SelectedValue);

        Transfer transfer = new Transfer() { TransferId = transferid };

        db.Entry(transfer).State = System.Data.Entity.EntityState.Deleted;

        db.SaveChanges();
     } 
}

Filtering the Transfers

The following code filters the transfers by a chosen team and or player. If you have trouble understanding the following code, and want to learn more about IQueryable please refer to this tutorial.

using (var db = new FootballDbContext())
{
    IQueryable<Transfer> allTransfers = db.Transfers;

    if (drp_PlayerFilter.SelectedIndex > 0)
    {
         int selectedPlayerId = Convert.ToInt32(drp_PlayerFilter.SelectedValue);
         allTransfers = allTransfers.Where(t => t.PlayerIdFK == selectedPlayerId);
    }

    if (drp_TeamFilter.SelectedIndex > 0)
    {
         int selectedTeamId = Convert.ToInt32(drp_TeamFilter.SelectedValue);
         allTransfers = allTransfers.Where(t => t.TeamIdFK == selectedTeamId);
    }
    grd_Transfers.DataSource = allTransfers.ToList();
    grd_Transfers.DataBind();
}