Code First One-to-Many (1:M) Relationships -Using Conventions and Data Annotations

This tutorial is about designing 1:M relationships using Code First Conventions and Data Annotations.

A Brief Look at 1:M Relationships

1:M relationship is the most common relationship type in database design, since it happens a lot in real world (a customer can have many credit cards but each credit card is assigned to a single person, or one professor (adviser) has many advisees but one advisee has only one adviser, etc.).

In 1:M relationships, a single record from the parent table (e.g., adviser) can possible relate to one or more child records in another table (e.g., advisee). However, the parent is not required to have child records; it CAN have 0 or 1, or more, but it does not have to. On the other hand, the child records HAVE to be associated with a parent record (it has be a SINGLE parent).

Below is an example table structure for a 1:M relationship between Customer and CreditCard. CreditCard is the dependent (or child) entity in this case, which means that it has to have a foreign key field (OwnerId) that actually corresponds to the CustomerId field in the parent table (Customer table).

Convention for One-to-Many (1:M) Relationships

To build 1:M relationship among entities in your domain model, you will need to use Navigation properties. I want to explain this with an example. Here is our scenario:

There are many soccer teams in the world. Each team contains many soccer players. However, each player is associated only with one team.

Based on this scenario, let’s create the classes needed in our model. First is Team class:

public class Team
{
     public int TeamId { get; set; }
     
     [MaxLength(50)]
     [Required]
     public string TeamName{ get; set; }

     public DateTime DateEstablished { get; set; }
}

And here is the Player class:

public int PlayerId { get; set; }

    [MaxLength(50)]
    [Required]
    public string FullName { get; set; }

    [Required]
    public DateTime BirthDate { get; set; }

    [Required]
    public DateTime LicenseDay { get; set; }
}

We have created the two entities (or classes) needed to build the domain model. However, at the moment, these two entities are not associated, and they know nothing about each other. To build the relationship between these two entities (which is a 1-to-Many relationship), we need to add navigation properties to both classes.

Here is Team class with the navigation property:

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

    [MaxLength(50)]
    [Required]
    public string TeamName { get; set; }

    public DateTime DateEstablished { get; set; }

    //Navigation property -> indicating the dependent (or child) entity
    public List<Player> Players { get; set; }
}

Here is Player class with the navigation property:

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

    [MaxLength(50)]
    [Required]
    public string FullName { get; set; }

    public DateTime BirthDate { get; set; }

    public DateTime LicenseDate { get; set; }

    //Navigation property -> indicating the parent table
    public Team CurrentTeam { get; set; }
}

A team can have many players and the Team class has a List<Players> property to allow you to get the list of players for a specific team. Additionally, the Player class has a Team property, so that you can identify Team associated with a particular Player. Code First recognizes this one-to-many relationship between Team and Player. Based on Code First conventions, Entity Framework can interpret the model and build a database where the Players table has foreign key by which you can know which Team a Player belongs to.

Please install entity framework by executing the following command in Package Manager Console (Tools -> NuGet Package Manager -> Package Manager Console):

PM> install-package entityframework

Now, we need to create the DbContext class for our application:

public class SoccerDbContext: DbContext
{
    public SoccerDbContext(): base("SoccerDatabase"){  }
     
    public List<Player> Players { get; set; }
     
    public List<Team> Teams { get; set; }
}

Please make sure you have a proper connection string with name “SoccerDatabase” in Web.Config file, and you added App_Data folder to your project (assuming that you used |DataDirectory| in the connection string). Here is a sample connection string:

<connectionStrings>
 <add name="SoccerDatabase" connectionString="Data Source=(LocalDB)\v11.0; AttachDbFilename=|DataDirectory|\SoccerDbFile.mdf; Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Please enable migrations (enable-migrations -contexttype SoccerDbContext) and use Seed() method to feed the database with some test data:

protected override void Seed(_1toM1BC.Model.SoccerDbContext context)
{
  context.Teams.AddOrUpdate(t => t.TeamName,
    new Team { TeamName = "Fenerbahce", DateEstablished = new DateTime(1905, 1, 1) },
    new Team { TeamName = "Barcelona", DateEstablished = new DateTime(1901, 1, 1) },
    new Team { TeamName = "Juventus", DateEstablished = new DateTime(1925, 1, 1) }
  );
  context.SaveChanges();
}

We seed the database with several teams, we will create an interface to create players and add them to the Teams.

Please add a migration item (add-migration firstDbCreate) and update the database (update-database) using Package Manager Console. In the migration class, you should have the following code:

After you executed the update-database command, then you should have your database created:

And, you may notice that in Players table CurrentTeam_TeamId is the foreign key referencing the TeamId in Teams (parent) table:

We did not define a property in Player class to be the foreign key of this relationship; in other words there is NO property in Player class that can be the foreign key in the database pointing back to the associated team. However, thanks to the Navigation properties, Code First was able to infer that there is a 1:M relationship between Player and Team entities, where Player is the child or dependent and Team is the parent. It, then, created a foreign key in the database using the pattern [Name of navigation property]_[Primary Key of related class] (i.e.,CurrentTeam_TeamId). Also note that the foreign key is nullable, meaning that a player does not have to be associated with a team.

Adding new records in 1:M relationships

Let’s create a form where we can add a new player. In this form, the existing teams should be listed in a dropdownlist. Your form may look like this:

First thing we need to do is to bind the existing teams to the dropdown list in Page_Load:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        SoccerDbContext db = new SoccerDbContext();
        drp_Teams.DataSource = db.Teams.ToList();
        drp_Teams.DataTextField = "TeamName";
        drp_Teams.DataValueField = "TeamId";
        drp_Teams.DataBind();
    }
}

In the code above, we created an instance from SoccerDbContext class, named db. Then, we used db.Teams to retrieve the list of teams from the database. You may remember that List<Team> Teams was defined in the SoccerDbContext class. We can use Teams property to access and query the records in Teams table in the database; Teams property act like a proxy to the Teams table in the database.

Now, let’s write the code for creating a new Player in the database. Please double click on Add Player button in the form to navigate to the click event handler in the CodeBehind file:

protected void btn_Add_Click(object sender, EventArgs e)
{
    Player myPlayer = new Player();
    myPlayer.FullName = txt_Name.Text;
    myPlayer.BirthDate = DateTime.Parse(txt_Birth.Text);
    myPlayer.LicenseDay = DateTime.Parse(txt_Lisence.Text);

    int currTeamId = Convert.ToInt32(drp_Teams.SelectedValue);
    Team currTeam = db.Teams.Single(t => t.TeamId == currTeamId);
    myPlayer.CurrentTeam = currTeam;

    db.Players.Add(myPlayer);
    db.SaveChanges();
}

In the code above, you may notice that assigning a value to the CurrentTeam property of Player class a little bit troublesome. This is because CurrentTeam is a type of Team however we do not have the associated Team instance in the memory when creating a new player. All we know is the Id of the selected team (via the SelectedValue property of drp_Teams control). However, this is not enough; we need to have the corresponding Team instance, which then can be assigned to the CurrentTeam property of myPlayer. That is why we had to query the database by using Single method of Linq to retrieve the selected Team instance and load it to the memory.

One problem with this approach is that you do two database calls each time you create a player. However, the less frequently you connect to the database, the better for your application’s performance will be.

Instead, we can create a foreign key property in Player class, and that property could be used to read and set the current team of the player. Let’s update our Player class as follows:

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

    [MaxLength(50)]
    [Required]
    public string FullName { get; set; }

    public DateTime BirthDate { get; set; }

    public DateTime LicenseDay { get; set; }

    //Navigation property -> indicating the parent table
    public Team CurrentTeam { get; set; }

    [Required]
    [ForeignKey("CurrentTeam")]
    public int CurrentTeamId { get; set; }
}

As you see above, we created a new property called CurrentTeamId, and we used ForeignKey data annotation to indicate that CurrentTeamId is a foreign key linked to CurrentTeam navigation property. Entity Framework is smart enough to associate CurrentTeamId with the primary key of Team class (i.e., TeamId) and implement this in the database. If we did not have ForeignKey annotation, then Entity Framework would treat CurrentTeamId just like another property, and would still generate CurrTeam_TeamId foreign key based on default conventions.

INFORMATION: We would not have to use ForeignKey data annotation, if we followed one of the default conventions:

[Target Type Key Name] : TeamId
[Target Type Name] + [Target Type Key Name] : Team_TeamId
[Navigation Property Name] + [Target Type Key Name] : CurrentTeam_TeamId

Please add a new migration (add-migration FKchange) and update the database (update-database) by using Package Manager Console.

This is how your new database should look like this:

With this update, we can change the code for creating a new player:

protected void btn_Add_Click(object sender, EventArgs e)
{
    Player myPlayer = new Player();
    myPlayer.FullName = txt_Name.Text;
    myPlayer.BirthDate = DateTime.Parse(txt_Birth.Text);
    myPlayer.LicenseDay = DateTime.Parse(txt_Lisence.Text);
    myPlayer.CurrentTeamId = Convert.ToInt32(drp_Teams.SelectedValue);

    db.Players.Add(myPlayer);
    db.SaveChanges();
}

Fluent API

If you check the latest structure of your database, you will see ON DELETE CASCADE statement:

This tells that when a team is deleted from the Teams table, the players in that team will be also deleted automatically; in other words the delete action on Teams table will be cascaded on Players, on its children.

Unfortunately, there is no way to configure this differently by using Data Annotations. This is where Fluent API is mandatory. We will learn Fluent API in the next tutorial.

Improving our Model by another 1:M Relationship

Let’s improve our domain model by including the League entity. There is a 1:M relationship between Team and League: each soccer team plays in a specific league, and each league contains many teams.

Here is the definition of the League class:

public class League
{
     public int LeagueId { get; set; }

     public string LeagueName { get; set; }

     public int TeamCount { get; set; }

     //Navigation property -> Each League has many Teams
     public List<Team> Teams { get; set; }
}

In the code above, you may notice that we have a Navigation property to indicate that each team can have many teams.

We need to add another navigation property to Team class to define the other end of this 1:M relationship. Here is the updated Navigation properties of Team class:

//Navigation property -> Each team has many players
public List<Player> Players { get; set; }

//Navigation property -> Each team belongs to a specific league
[ForeignKey("LeagueId")]
public League League { get; set; }

//Foreignkey -> The associated LeagueId
public int LeagueId { get; set; }

Please use Migrations to updated the database. Here is  how your database should look like:

Inverse Navigation Properties

In the 1:M example we covered, there were one navigation property in each class, and these navigation properties were enough to set up the 1:M relationship between these two classes thanks to default code first conventions. For example, Player class has a single property that points to Team (parent table), and similarly Team has a single property that points to Players (child table).

However, there happens to be multiple relationships between two classes, which makes entity framework confused about setting up the relationships properly. Because, multiple relationships will require more than one navigation property on each end of the relationship. In these cases, we need to do some configuration to help entity framework figure out which navigation property in parent entity references to which navigation property in the child entity.

For example, let’s assume that we want to keep track of the original team where the player has started to play soccer professionally (I am aware that this is not a good idea in terms of design, just bear with me). Here is the navigation properties for the updated entities:

Here is the navigation properties in Team class:

public List<Player> AllPlayers { get; set; }
public List<Player> OriginalPlayers { get; set; } 

Here is the navigation properties in Player class:

public Team CurrentTeam { get; set; }
[ForeignKey("CurrentTeam")]
public int? CurrentTeamId { get; set; }

public Team OriginalTeam { get; set; }
[ForeignKey("OriginalTeam")]
public int? OriginalTeamId { get; set; }

Let’s update the database using migrations, here is how the database should look like after the updates:

What happened is that Entity Framework created every possible foreign key for every possible match between the navigation properties in two entities. Because, it cannot know which navigation property in Team entity is linked to which navigation property in Player class.

With data annotations, we can present this information (the links among the navigation properties in two classes), by using InverseProperty. Let’s configure the Team class with InverseProperty data annotation:

[InverseProperty("CurrentTeam")]
public List<Player> AllPlayers { get; set; }

[InverseProperty("OriginalTeam")]
public List<Player> OriginalPlayers { get; set; }

In the above code, we added InverseProperty to link the navigation properties AllPlayers and OriginalPlayers back to the corresponding navigation properties in Player class, CurrentTeam, and OriginalTeam. That is, we provided the name of the corresponding property through InverseProperty.

Please note that you could use InverseProperty in Player class as well, and point to the corresponding navigation property in Team class. You need to use InverseProperty at one end of the relationship.

Here is our updated database:

As you may see, foreign keys are set properly in the database.