Creating the Data Models, and explaining a little bit about primary keys and foreign keys.

Creating the Data Model

Contoso University calls for the creation of three entities: Course, Enrollment, and Student. These entities are placed in separate class files, so I created Course.cs, Enrollment.cs, and Student.cs.

Below are the three entities and how they relate to each other, using the image in the tutorial:

15 - Original Entities

What’s In An Entity

The Course.cs file has three Properties and one Navigation Property. Each class file will basically correspond to a database table, and each property maps to a column in that table. Here, when the database is created, it will create a Course table with columns labeled “CourseID”, “Title” and “Credits”. Each object created using this class would correspond to a row in that database table.

The ID Field: The Point of a Primary Key

I’ve taken classes on databases, but for the uninitiated, the “ID” field is used as the Primary Key (PK). The PK is the sort of social security number of the  row; it uniquely identifies that row in the table behind the scenes. It’s not a column meant to be shown to a user, and it’s not something you define manually —  the database will assign a unique number for this value automatically as an object (row) is created.

  • Using Entity Framework, a property called “ID” or “ClassnameID” (in this case, CourseID) will be used as the Primary Key.
Relationships Between Entities: Navigation Properties

Relational databases work using defined relationships between different entities. We have three entities, but we need to show how each entity relates to each other – that is what the above image shows.

Each Student entity corresponds to one student. One student entity holds a Primary Key (“ID”), Last Name, First and Middle Name, and an Enrollment Date. Each student may enroll in one or more courses, so we need a way to record and show a list of which Courses a Student has Enrolled in.

The tutorial defines a list, in this case an ICollection called Enrollments, to hold multiple enrollment records related to that student. Note the data type in the ICollection, which is Enrollment. This links the Student entity and Enrollment entity together – only Enrollments directly related to this StudentID would be retrieved.

using System;
using System.Collections.Generic;

namespace ContosoUniversity.Models
{
    public class Student
    {
        public int ID { get; set; }
        public string LastName { get; set; }
        public string FirstMidName { get; set; }
        public DateTime EnrollmentDate { get; set; }
        
        public virtual ICollection<Enrollment> Enrollments { get; set; }
    }
}
The Enrollments Entity and Foreign Keys

The Enrollment.cs entity, you’ll notice, has three “ID” fields: EnrollmentID, CourseID and StudentID, and only one other field called Grade.

EnrollmentID is the Primary Key of the entity as you might guess. The other two are Foreign Keys (FK). These are references to the ID fields of other entities. One Enrollment entity hold one ID field, a reference to one Course, a reference to one Student, and a Grade for that Enrollment. The Foreign Key defines a relationship between database tables. The ICollection list called Enrollments, which we defined in Student.cs, also acts as a sort of foreign key.

Here is the Enrollments Entity defined in my program along with comments explaining each:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TeamRoster.Models
{
    public enum Grade
    {
        A, B, C, D, F
    }

    public class Enrollment
    {
        // Primary Key. EF will use a property marked either ID or classID
        public int EnrollmentID { get; set; }

        // Foreign Keys. These properties reference the primary keys of other entities
        // EF Naming Convention: <Navigation Property Name><Primary Key Name>
        //      Here, it's CourseID because Navigation Property is Course, and
        //      the Course primary key is ID.
        public int CourseID { get; set; }
        public int StudentID { get; set; }

        // The ? after Grade marks this property as nullable
        public Grade? Grade { get; set; }

        // Navigation Properties
        // The "type" references a class, i.e. TeamRoster.Models.Student
        public virtual Course Course { get; set; }
        public virtual Student Student { get; set; }

    }
}

The Course.cs entity works like the Student.cs entity does – an ID property, two more properties for data exclusive to each Course (Title, Credits), and a Navigation Property to create a list of Enrollments to this course. It must be a list because multiple students may enroll for each course.

Creating My Own Entities

My TeamRoster extension of this code involves creating two other entities: Team.cs, which will hold information on each baseball team, and Player.cs, which holds information about the players on each team.

Below is the EF Designer Model of the two entities:

16 - TeamRoster Entity Model

My Team.cs Model file holds an ID field for the Primary Key, the team’s Name and City, and a Navigation Property that will hold the Player entities related to this Team. Since more than one Player is on each Team, that Navigation Property must be in List form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Web;

namespace TeamRoster.Models
{
    public class Team
    {
       
        public int ID { get; set; }

        [DisplayName("Team Name")]
        public string Name { get; set; }
        public string City { get; set; }

        // I want to reference the players on each team.
        // 1 team can have many players
        public virtual ICollection<Player> Players { get; set; }
    }
}
Defining the Player Model
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Web;

namespace TeamRoster.Models
{
    public enum Position
    {
        Catcher, Pitcher,
        FirstBase, SecondBase, Shortstop, ThirdBase,
        LeftField, CenterField, RightField
    }

    public enum BattingSide
    {
        Left, Right
    }

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

        [DisplayName("Last Name")]
        public string LastName { get; set; }

        [DisplayName("First Name")]
        public string FirstName { get; set; }
        public int Age { get; set; }

        [DisplayName("Number")]
        public int PlayerNumber { get; set; }
        
        // The player's team
    
        public int TeamID { get; set; }

        public Position Position { get; set; }
        public BattingSide Hits { get; set; }

        // Navigation Property. Each player can only be on 1 team
        // TeamID above gets its value from this Team property.
        public virtual Team Team { get; set; }

    }
}

From top to bottom in this Player.cs model class, first listed are two enums. These are essentially arrays of text choices, one for the player’s position, one for which side the player hits on. When creating or editing this player’s info, these properties will show as pulldown menus, so that only the listed options are available  — this saves me from having to manually type in “Catcher” or “Pitcher” for position, ensuring that I don’t fat-finger a key and put in something invalid.

There’s an ID property for the primary key, and fields for Last Name and First Name. The TeamID field will show which team this Player is associated with. The Position and Hits properties make use of the enums defined above it.

A Navigation property called Team is created to link this table back to Team.cs. Each player can only be on one Team, so this doesn’t need to be a List. This Team property isn’t holding one piece of info though; it’s going to hold the whole Team entity related to this player. Therefore, the Navigation Property is defined as type Team, rather than type INT or STRING.

Study Note: ENUM traps

When creating the Position Enum above, I first tried to create positions the way you’d see them in a baseball game: 1B, 2B, SS, 3B, P, C, LF, CF, RF. My app wouldn’t build when I tried this. Turns out, the values can’t start with numbers.

Okay, so I tried writing out the position names – “First Base”, “Second Base”, “Third Base” – and this wouldn’t work either. A little reading uncovered that enums must follow naming conventions for variables, so spaces don’t work either. I settled for the way they’re written out above, with the spaces removed.

Of course, down the line once the Controllers are created and the app runs, the labels for these fields don’t have spaces either – they’ll show as “FirstBase” and “SecondBase” on the app.

The [DisplayName] markers above certain fields are there to deal with this issue, which will be covered in the next study log.

What’s Up Next

The next study log covers the database connections and the creation of the controller files.