In this tutorial, I discuss how to add/remove entities in M:M relationships (entity framework code first) through an example. This tutorial covers following topics:
- Creating and Configuring M:M Relationships by using Fluent API,
- Adding/Deleting records in M:M Relationships with Connected/Disconnected Approaches,
- Loading Related Entities,
- Writing complex Linq queries in M:M Relationships,
- Adding/Updating/Deleting simple entities, Competitions in this tutorial, with Entity Framework Code-First
Let’s first see how the final application will look like.
The Description of the Application
We will build a page to manage Competitions in a football application. Initially, the competitions page will include a Grid View (with Select buttons) that lists the existing competitions. There will be also a link (New Competition?) under the grid view, and this link will show the form for adding a new competition.
When a competition is selected from the grid view, the following panel with blue background should be displayed.
In this panel there are two sections:
- An update form where the user can update (or delete) the selected competition (note that the form is populated with the existing information of the competition)
- A team registration form where you the user can register/unregister teams for the selected competition.
Last, there will be a form for adding a new competition record. This form will be displayed when user clicks on “New Competition?” link:
Model
Our model is composed of two classes: Competition and Team. There is a many-to-many relationship between these two entities. Here are the class declaration of these two classes:
public class Competition
{
public int CompetitionId { get; set; }
public string CompetitionName { get; set; }
public bool IsInternational { get; set; }
public int Year { get; set; }
public List ParticipatingTeams { get; set; }
}
public class Team
{
public int TeamId { get; set; }
public string TeamName { get; set; }
public int YearEstablished { get; set; }
public List Competitions { get; set; }
}
Fluent API for configuring the relationship
The fluent api sets up the M:M relationship between Team and Competition entities. If you have trouble understanding the following code please refer to the previous tutorial.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasMany(c => c.ParticipatingTeams)
.WithMany(t => t.Competitions)
.Map(
m => m.ToTable("CompetitionsOfTeams")
.MapLeftKey("CompetitionId")
.MapRightKey("TeamId")
);
}
INFORMATION
Please note that the following section first explains how to select a row in grid view and add/delete entry in M:M relationships. Then, it describes the other parts of the application, such as adding/deleting/updating competition entities.
Adding/Deleting in M:M Relationships: Add/Remove a Team to/from a Competition
Displaying existing Competitions with GridView
In the code below, we create a method named BindCompetitions() and called this method inside Page_Load. BindCompetitions() method uses Competitions dbset defined in dbcontext class to retrieve the existing competition records (db.Competitions.ToList()), then uses DataSource property of Grid View to attach the competition records to the grid view, and then executes BindData() method to bind the attached records to the grid view.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindCompetitions();
}
}
public void BindCompetitions()
{
using (var db = new FootballDbContext())
{
grd_Competitions.DataSource = db.Competitions.ToList();
grd_Competitions.DataBind();
}
}
Enabling Select Button in GridView
To be able to perform any operation on a competition, first we need to be able to select it from the grid view. We can use GridView property called AutoGenerateSelectButton property to automatically generate Select button per each grid view row. Below is the html mark up of the grid view:
<GridView ID="grd_Competitions" AutoGenerateSelectButton="true" DataKeyNames="CompetitionId" OnSelectedIndexChanged="grd_Competitions_SelectedIndexChanged" CssClass="table-condensed table-bordered" runat="server">
<SelectedRowStyle BackColor="WhiteSmoke" Font-Bold="true"/>
</asp:GridView>
- AutoGenerateSelectButton = “true” will attach a Select button at the beginning of each row,
- DataKeyNames=”CompetitionId” will attach the competitionid key value to each row which points to a competition instance, this way we will be able to read the id of the selected competition instance (i.e., row) and use this id for retrieving the registered teams and to add to or remove from competitions,
- OnSelectedIndexChanged=”grd_Competitions_SelectedIndexChanged” will create an event handler and this handler will be executed when a user selects a row (i.e., Competition).
- SelectedRowStyle tag help us format the selected rows so that we can easily identify the selected row.
Adding/Removing Teams to/from Competitions
Now we will learn how to add a team to a competition, or how to remove a team from a competition. Our focus will be the buttons highlighted in the following image. We will implement how to list the existing teams and registered teams in the next step.
Registering a Team with Connected Approach
In this part, we will learn how to add a team to a competition with connected approach. With this approach we will directly work on entities that are fetched from the database and attached to the dbcontext.
protected void btn_Register_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
//get the id of the selected team from the listbox (on the left)
int selectedTeamId = Convert.ToInt32(lst_AllTeams.SelectedValue);
//
//Fetch the team entity from the database and attach it to the context.
Team myTeam = db.Teams.Single(c => c.TeamId == selectedTeamId);
//
//
//get the id of the selected competition from the grid view
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue);
//
//Fetch the competition entity from the database and attach it to the context
Competition myComp = db.Competitions.Single(c => c.CompetitionId == selectedCompId);
//
//add the competition to the Competitions list of myTeam instance
myTeam.Competitions.Add(myComp);
//save changes
db.SaveChanges();
};
//This method is implemented in the following sections.
BindTeamForCompetitions();
}
The code above is actually straightforward. We have our Team instance and Competition instance attached to the context properly, and we add the Competition instance to the Competitions collection of the Team instance.
However, this will not work. If you attempt to test the click event above, you should see the following NullReferenceException error:
To better understand the source of this error, let’s closely examine the myTeam instance. As you may see, its Competitions is not instantiated and it has a null reference.
That is, db.Teams did not bring the related data of the Team instance. What db.Teams does is to map ONLY the Teams table in the database to a list, and not fetch any related records from other tables by default. We have to explicitly tell entity framework that we want Competitions collection of the team to be loaded as well so that we can have a valid Competitions collection and add our new competition to this list.
Loading related data
To fetch a Team from the database along with its Competitions, we need to use Include method. Include method receives the Navigation property name as the parameter. You may check the Team class, you will see that there is a navigation property named Competitions, pointing to the Competition entity which is the related entity of the Team that we want to load.
Team myTeam = db.Teams
.Include("Competitions")
.Single(c => c.TeamId == selectedTeamId);
If you want to use Lambda expression to point to the navigation property, you will need to include the following library in your page:
using System.Data.Entity;
Then, you can use lambda expression Include() method like this:
Team myTeam = db.Teams
.Include(t=>t.Competitions)
.Single(c => c.TeamId == selectedTeamId);
The advantage of using Lambda expression in Include method is that you can correctly choose the Navigation property from the provided list. However, when you use the string Navigation property name as parameter, you need to remember it correctly.
After updating the code with Include() method, let’s test the application. If you debug your code, you should see that Competitions collection of myTeam instance is not null.
Registering a Team with Disconnected Approach
The following does the exact same thing that we have just done in the previous code. However, compared to previous approach, it does not connect to the database until registering the team to the selected competition at the end of the code. Please see the comments provided within the code for the explanation.
using (var db = new FootballDbContext())
{
int selectedTeamId = Convert.ToInt32(lst_AllTeams.SelectedValue);
//Team instance with only TeamId provided
Team myTeam = new Team() { TeamId = selectedTeamId };
//Attach myTeam to the context with state set to Unchanged
//The state needs to be 'unchanged'
//because we don't want to update/add/delete myTeam
db.Entry(myTeam).State = System.Data.Entity.EntityState.Unchanged;
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
//Competition instance with only CompetitionId provided
Competition myComp = new Competition() { CompetitionId = selectedCompId };
//Attach myComp to the context with state set to Unchanged
//The state needs to be 'unchanged'
//because we don't want to update/add/delete myComp
db.Entry(myComp).State = System.Data.Entity.EntityState.Unchanged;
//We initiate the List<Competition> with one value: myComp
myTeam.Competitions = new List<Competition>() { myComp };
db.SaveChanges();
};
SQL Query Approach
Or, as another disconnected approach, we could write an sql query to add a new record to the JunctionTable, which is CompetitionsOfTeams as configured in the fluent api.
db.Database.ExecuteSqlCommand("INSERT INTO CompetitionsOfTeams(CompetitionId, TeamId)" +
" VALUES(" + selectedCompId + ", " + selectedTeamId + ")");
If you are familiar with writing SQL queries, this approach might be very convenient to implement. This approach is the fastest one introduced in this tutorial.
Unregistering a Team with Connected Approach
The code for removing a team from a competition is exactly the same with the code for adding a team to a competition. The only difference is that instead of Add() method (myTeam.Competitions.Add()) we will use Remove() method (myTeam.Competitions.Remove()). Please see the details of the code below:
protected void btn_UnRegister_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
int selectedTeamId = Convert.ToInt32(lst_RegisteredTeams.SelectedValue);
Team myTeam = db.Teams.Include("Competitions").Single(c => c.TeamId == selectedTeamId);
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
Competition myComp = db.Competitions.Single(c => c.CompetitionId == selectedCompId);
//
//this is where we use Remove method instead of Add
myTeam.Competitions.Remove(myComp);
db.SaveChanges();
};
BindTeamForCompetitions();
}
Unregistering a Team with Disconnected Approach
The following code executes a SQL delete command to unregister the team.
db.Database.ExecuteSqlCommand("DELETE FROM CompetitionsOfTeams " +
"WHERE CompetitionId=" + selectedCompId +
" AND TeamId=" + selectedTeamId);
Advance Linq Queries in M:M Relationships: Filtering Teams by the Selected Competition
The following code illustrates how to generate
- the list of Teams that registered to the selected competition (regTeams),
- the list of Teams that are not registered to the selected competition (unregTeams).
After generating these lists, it binds them to the corresponding list controls.
public void BindTeamForCompetitions()
{
using (var db = new FootballDbContext())
{
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
//This fetches ALL teams with no filter
//along with the Competition entities attached
//thanks to Include() method
List<Team> allTeams = db.Teams
.Include(t => t.Competitions)
.ToList();
List<Team> regTeams = new List<Team>();
foreach (Team team in allTeams)//iterate through teams
{
//this will create a collection composed of
//the IDs of the competitions in which
//the current team participates, e.g., [1, 4]
List<int> comps = team.Competitions.Select(c => c.CompetitionId);
//
//Then we check if this collection of IDs contains
//the selected competition id, if it contains
//this means the current team is registered to
//the competition, and
//then we add this team to the regTeams list
if (comps.Contains(selectedCompId))
regTeams.Add(team);
}
List<Team> unregTeams = new List<Team>();
//We can use Except() method to filter out the
//registered teams from all teams,
//leaving only the teams that are not registered
//to the selected competition
unregTeams = allTeams.Except(regTeams).ToList();
lst_AllTeams.DataSource = regTeams
lst_AllTeams.DataValueField = "TeamId";
lst_AllTeams.DataTextField = "TeamName";
lst_AllTeams.DataBind();
lst_RegisteredTeams.DataSource = unregTeams;
lst_RegisteredTeams.DataValueField = "TeamId";
lst_RegisteredTeams.DataTextField = "TeamName";
lst_RegisteredTeams.DataBind();
};
}
The approach followed above is actually not very performance effective. This is because we fetched all the teams from the database to the server memory, and do several operations on the existing teams to generate two list of teams: registered and unregistered teams. And, all these happened in the server memory.
Instead, we can write a single linq query to retrieve the registered teams like this:
List<Team> regTeams = new List<Team>();
regTeams = db.Teams
.Where(
team => team.Competitions
.Select(c => c.CompetitionId)
.Contains(selectedCompId)
).ToList();
The code above actually repeats the same code that we wrote previously, just with different structure. The following visual illustrates the code common in two approaches:
Similar to foreach, the Where() also iterates through the Teams, and for each team item it checks if the IDs of the competitions participated by the team includes the id of the selected competition by using Contains() method. If it contains, then Where() will automatically include the team instance in the result set. The same was achieved with the previous approach by using a IF condition and regTeams.Add(team).
Writing a single Linq statement with Lambda Expression will have substantial performance gains because it will filter the teams in the database server, preventing processing and memory costs in the server. That is, it will generate a single sql query and execute it in the database sever, and fetch the filtered results to the server memory.
We can get the list of teams which are not registered in the selected competition with a very similar query. Only difference is ” ! ” (not) :
List<Team> unregTeams = new List<Team>();
unregTeams = db.Teams
.Where(
team => !team.Competitions
.Select(c => c.CompetitionId)
.Contains(selectedCompId)
).ToList();
The Rest of the Implementation
Adding a New Competition
The following code illustrates how to add a new Competition record. Short explanations are provided within the text.
protected void btn_CreateCompetition_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
//first create a new instance of competition with the user input
Competition newComp = new Competition()
{
CompetitionName = txt_CompetitionName.Text,
IsInternational = chk_IsInternational.Checked,
Year = Convert.ToInt32(txt_Year.Text)
};
//using dbcontext insert the new competition instance to the database
//by using Competitions dbset declared in the dbcontext class
db.Competitions.Add(newComp);
//
//or, instead of db.Competitions.Add(newComp) you could use
//the following for the same purpose, it is the same:
//db.Entity(newComp).State = System.Data.Entity.EntityState.Added;
db.SaveChanges();
//bind existing competitions to the grid view
BindCompetitions();
}
}
//Click event handler of "New Competition?" button
protected void lbtn_ShowForm_Click(object sender, EventArgs e)
{
//Display/hide the form
pnl_NewCompetitionForm.Visible = !pnl_NewCompetitionForm.Visible;
}
Updating/Deleting a Competition
As you may remember, when a competition is selected from the grid view, the following blue panel should be displayed.
Populate Update Form
The following code is the handler for OnSelectedIndexChanged event.
protected void grd_Competitions_SelectedIndexChanged(object sender, EventArgs e)
{
//this the panel with blue background
pnl_ActionSet.Visible = true;
using (var db = new FootballDbContext())
{
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
//Find() method fetches the entry from the database based on the provided id
Competition myComp = db.Competitions.Find(selectedCompId);
if (myComp != null)
{
txt_NewCompetitionName.Text = myComp.CompetitionName;
txt_NewYear.Text = myComp.Year.ToString();
chk_NewIsInternational.Checked = myComp.IsInternational;
}
}
}
In the code above, we used Find() method to fetch the Competition record from the database based on the provided competition id. The Find() method is handy when a single entity needs to be fetched from the database and its id is known. Then, we read the properties of the Competition record and assign them to the corresponding fields.
Update Competition with Connected Approach
With connected approach, we need to first fetch the competition record that we want to update and store in the memory as a competition instance. Then, we need to assign the new values (provided by the user) to the corresponding properties of the competition instance.
protected void btn_Update_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
Competition updatedComp = db.Competitions.Single(c => c.CompetitionId == selectedCompId);
updatedComp.CompetitionName = txt_NewCompetitionName.Text;
updatedComp.Year = Convert.ToInt32(txt_NewYear.Text);
updatedComp.IsInternational = chk_NewIsInternational.Checked;
db.SaveChanges();
}
BindCompetitions();
}
Although this approach is simple to implement, it uses server memory inefficiently because it requires to load the entire competition instance to the memory although we will not use the values of its properties (e.g., competition name or year).
Update Competition with Disconnected Approach
With Disconnected approach, we create a new instance of Competition with the new user input. Only the CompetitionId of this competition instance cannot be determined by the user. CompetitionId should be set to the id of the selected competition that the user is updating. Then, we attach this instance (updatedComp) to the context with its state is set to Modified. When we call db.SaveChanges(), entity framework will locate the competition record based on the CompetitionId of updatedComp and update it with new user input.
protected void btn_Update_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
Competition updatedComp = new Competition()
{
CompetitionId = selectedCompId,
CompetitionName = txt_NewCompetitionName.Text,
Year = Convert.ToInt32(txt_NewYear.Text),
IsInternational = chk_NewIsInternational.Checked
};
db.Entry(updatedComp).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
BindCompetitions();
}
Delete Competition with Connected Approach
With connected approach, we need to first fetch the competition record that we want to delete and map this record to a competition instance. Then, we used the Remove() method of the Competitions dbset collection (in dbcontext class) to remove the competition instance.
protected void btn_Delete_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
Competition myComp = db.Competitions.Single(c => c.CompetitionId == selectedCompId);
db.Competitions.Remove(myComp);
db.SaveChanges();
};
BindCompetitions();
pnl_ActionSet.Visible = false;
}
Although this approach is simple to implement, it uses server memory inefficiently because it requires to load the entire competition instance to the memory although we did not use the values of its properties (e.g., competition name or year).
Delete Competition with Disconnected Approach
With Disconnected approach, we create a new instance of Competition with its id set to the key value of the selected competition. Then, we attach this instance (compToDelete) to the context with its state set to Deleted. When we call db.SaveChanges(), entity framework will locate the competition record based on the CompetitionId of compToDelete and delete it from the Competitions table.
protected void btn_Delete_Click(object sender, EventArgs e)
{
using (var db = new FootballDbContext())
{
int selectedCompId = Convert.ToInt32(grd_Competitions.SelectedValue.ToString());
Competition compToDelete = new Competition()
{
CompetitionId = selectedCompId
};
db.Entry(compToDelete).State = EntityState.Deleted;
db.SaveChanges();
};
BindCompetitions();
pnl_ActionSet.Visible = false;
}
Asp.Net (C#) and Entity Framework Code-First 