Introduction
This is the first part of building ASP.NET Web API RESTful Service Series. The topics we’ll cover are:
Update (2014-March-5) Two new posts which cover ASP.NET Web API 2 new features:
Building the Database Model using Entity Framework Code First
We'll be using Entity Framework Code First approach where we’ll define our model objects using Plain Old CLR Objects POCO. We’ll be code centeric and start by writing standard .NET classes which define the domain model objects that are suitable for our API. Those POCO classes will be responsible to generate our eLearning database.
The eLearning database is simple, we want to be able to define and store Students and Tutors. As well, we have to define and store Courses and Subjects. We need to allow each Student to enroll in different Courses.
The image below shows the final result of the database schema, I’m listing it early so it will facilitate the understanding of the POCO classes we’ll build now:
Step 1: Create a new empty Class Library Project
We’ll start by creating a new empty class library project which will be responsible for all data management operations (Data Layer). Choose File->New Project->Windows->Class Library and name your solution “eLearning” and your class library “Learning.Data”. You can choose .NET framework 4 or 4.5.
Step 2: Install Entity framework using NuGet
We need to install Entity framework version 5 or 6 using NuGet package manager or NuGet package console, the package we’ll install is named “EntityFramework“. Our solution will look as below after installing Entity Framework:
Step 3: Creating our Model
As we stated before, we do not have our eLearning database and we need to create it by writing standard .NET classes that define the domain model objects.
Now, add a new folder called Entities, then add five classes called Subject, Course, Tutor, Student, and Enrollment. Those classes contain just simple properties and will shape our database:
public class Subject
{
public Subject()
{
Courses = new List<Course>();
}
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Course> Courses;
}
public class Course
{
public Course()
{
Enrollments = new List<Enrollment>();
CourseTutor = new Tutor();
CourseSubject = new Subject();
}
public int Id { get; set; }
public string Name { get; set; }
public Double Duration { get; set; }
public string Description { get; set; }
public Tutor CourseTutor { get; set; }
public Subject CourseSubject { get; set; }
public ICollection<Enrollment> Enrollments { get; set; }
}
public class Tutor
{
public Tutor()
{
Courses = new List<Course>();
}
public int Id { get; set; }
public string Email { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Enums.Gender Gender { get; set; }
public ICollection<Course> Courses;
}
public class Student
{
public Student()
{
Enrollments = new List<Enrollment>();
}
public int Id { get; set; }
public string Email { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Enums.Gender Gender { get; set; }
public DateTime DateOfBirth { get; set; }
public DateTime? RegistrationDate { get; set; }
public DateTime? LastLoginDate { get; set; }
public ICollection<Enrollment> Enrollments { get; set; }
}
public class Enrollment
{
public Enrollment()
{
Student = new Student();
Course = new Course();
}
public int Id { get; set; }
public DateTime EnrollmentDate { get; set; }
public Student Student { get; set; }
public Course Course { get; set; }
}
As you noticed, those classes do not derive from any base classes nor have any attributes, having those standard classes give us more data access flexibility and allow us to focus on the application needs without worrying about persistence implementation.
Entity framework Code First by default supports an approach called “Convention over Configuration” for mapping your POCO classes to database objects (Tables, Table fields data types, and FK Relations). I find this approach is useful in scenarios where you are building a demo/simple applications. But in our case we need to override this convention by providing custom database mapping rules using Fluent API.
Step 4: Applying Custom Mapping Rules
Once we apply the custom mapping rules, we will be able to define datatype for each column, set null-ability, map FK relationships between tables, and specify PK and Identity columns.
To do this, we need to create new folder named “Mappers” then add five classes which derive from System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<T>.
Classes are: CourseMapper, EnrollmentMapper, StudentMapper,
SubjectMapper, and TutorMapper.
class CourseMapper : EntityTypeConfiguration<Course>
{
public CourseMapper()
{
this.ToTable("Courses");
this.HasKey(c => c.Id);
this.Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(c => c.Id).IsRequired();
this.Property(c => c.Name).IsRequired();
this.Property(c => c.Name).HasMaxLength(255);
this.Property(c => c.Duration).IsRequired();
this.Property(c => c.Description).IsOptional();
this.Property(c => c.Description).HasMaxLength(1000);
this.HasRequired(c => c.CourseSubject).WithMany().Map(s => s.MapKey("SubjectID"));
this.HasRequired(c => c.CourseTutor).WithMany().Map(t => t.MapKey("TutorID"));
}
}
class EnrollmentMapper : EntityTypeConfiguration<Enrollment>
{
public EnrollmentMapper()
{
this.ToTable("Enrollments");
this.HasKey(e => e.Id);
this.Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(e => e.Id).IsRequired();
this.Property(e => e.EnrollmentDate).IsRequired();
this.Property(e => e.EnrollmentDate).HasColumnType("smalldatetime");
this.HasOptional(e => e.Student).WithMany(e => e.Enrollments).Map(
s => s.MapKey("StudentID")).WillCascadeOnDelete(false);
this.HasOptional(e => e.Course).WithMany(e => e.Enrollments).Map(
c => c.MapKey("CourseID")).WillCascadeOnDelete(false);
}
}
class StudentMapper : EntityTypeConfiguration<Student>
{
public StudentMapper()
{
this.ToTable("Students");
this.HasKey(s => s.Id);
this.Property(s => s.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(s => s.Id).IsRequired();
this.Property(s => s.Email).IsRequired();
this.Property(s => s.Email).HasMaxLength(255);
this.Property(s => s.Email).IsUnicode(false);
this.Property(s => s.UserName).IsRequired();
this.Property(s => s.UserName).HasMaxLength(50);
this.Property(s => s.UserName).IsUnicode(false);
this.Property(s => s.Password).IsRequired();
this.Property(s => s.Password).HasMaxLength(255);
this.Property(s => s.FirstName).IsRequired();
this.Property(s => s.FirstName).HasMaxLength(50);
this.Property(s => s.LastName).IsRequired();
this.Property(s => s.LastName).HasMaxLength(50);
this.Property(s => s.Gender).IsOptional();
this.Property(s => s.DateOfBirth).IsRequired();
this.Property(s => s.DateOfBirth).HasColumnType("smalldatetime");
this.Property(s => s.RegistrationDate).IsOptional();
this.Property(s => s.RegistrationDate).HasColumnType("smalldatetime");
this.Property(s => s.LastLoginDate).IsOptional();
this.Property(s => s.LastLoginDate).HasColumnType("smalldatetime");
}
}
class SubjectMapper : EntityTypeConfiguration<Subject>
{
public SubjectMapper ()
{
this.ToTable("Subjects");
this.HasKey(s => s.Id);
this.Property(s => s.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(s => s.Id).IsRequired();
this.Property(s => s.Name).IsRequired();
this.Property(s => s.Name).HasMaxLength(255);
}
}
class TutorMapper : EntityTypeConfiguration<Tutor>
{
public TutorMapper()
{
this.ToTable("Tutors");
this.HasKey(s => s.Id);
this.Property(s => s.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(s => s.Id).IsRequired();
this.Property(s => s.Email).IsRequired();
this.Property(s => s.Email).HasMaxLength(255);
this.Property(s => s.Email).IsUnicode(false);
this.Property(s => s.UserName).IsRequired();
this.Property(s => s.UserName).HasMaxLength(50);
this.Property(s => s.UserName).IsUnicode(false);
this.Property(s => s.Password).IsRequired();
this.Property(s => s.Password).HasMaxLength(255);
this.Property(s => s.FirstName).IsRequired();
this.Property(s => s.FirstName).HasMaxLength(50);
this.Property(s => s.LastName).IsRequired();
this.Property(s => s.LastName).HasMaxLength(50);
this.Property(s => s.Gender).IsOptional();
}
}
By looking at the code above, you will notice that we are configuring each POCO class property (Datatype, Null-ability, PK and identity columns, and FK relations). Those configurations will be reflected on the database tables we are building. For more details about mapping/configuring fluent API, you can visit this
link.
The relationships between eLearning database tables are simple and described as given below:
- Each
Course has a Subject. - Each
Tutor can teach multiple Courses. - Each
Student can enroll in multiple Courses. So we’ll have a Many-to-Many table to persist the relation called Enrollment.
Step 5: Creating Context Class to Handle Database Persistence
Now, we need to add a new class named LearningContext which derives from class System.Data.Entity.DbContext:
public class LearningContext:DbContext
{
public LearningContext():
base("eLearningConnection")
{
Configuration.ProxyCreationEnabled = false;
Configuration.LazyLoadingEnabled = false;
Database.SetInitializer(new MigrateDatabaseToLatestVersion<LearningContext,
LearningContextMigrationConfiguration>());
}
public DbSet<Course> Courses {get;set;}
public DbSet<Enrollment> Enrollments { get; set; }
public DbSet<Student> Students { get; set; }
public DbSet<Subject> Subjects { get; set; }
public DbSet<Tutor> Tutors { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new StudentMapper());
modelBuilder.Configurations.Add(new SubjectMapper());
modelBuilder.Configurations.Add(new TutorMapper());
modelBuilder.Configurations.Add(new CourseMapper());
modelBuilder.Configurations.Add(new EnrollmentMapper());
base.OnModelCreating(modelBuilder);
}
}
The “LearningContext” class is responsible for three tasks which are:
- Exposing our POCO classes as
public <code>DbSet properties, this means that every POCO class is transferred to a database table. - Overriding the
OnModelCreating procedure which is used to apply custom mapping rules for each POCO class by adding the new configurations to the DbModelBuilder configurations. - In “
LearningContext” class constructor, we have implemented two things:
- Disabled the
ProxyCreationEnabled and LazyLoadingEnabled properties which are enabled by default. The Lazy Loading property enables loading the sub-objects of model up front, in our case we want to load them on demand. The Proxy Creation property is used in conjugation with Lazy Loading property, so if is set to false the LearningContext won’t load sub-objects unless Include method is called. - Configured the initialization and migration strategy of the database to migrate to latest version if a model has changed (i.e., a new property has been added). To implement this, we need to add a new class called
LearningContextMigrationConfiguration which derives from class System.Data.Entity.Migrations.DbMigrationsConfiguration<TContext>. The code listing is as below:
class LearningContextMigrationConfiguration : DbMigrationsConfiguration<LearningContext>
{
public LearningContextMigrationConfiguration()
{
this.AutomaticMigrationsEnabled = true;
this.AutomaticMigrationDataLossAllowed = true;
}
#if DEBUG
protected override void Seed(LearningContext context)
{
new LearningDataSeeder(context).Seed();
}
#endif
}
The LearningContextMigrationConfiguration class is responsible for two tasks which are:
- In the constructor of the class, we set the property
AutomaticMigrationsEnabled to true which means that we need EF to handle the automatic migration for us without caring about DB Versioning. As well, we set the property
AutomaticMigrationDataLossAllowed to true, this is dangerous to set in production environment. If it was set to false, an exception will be thrown if data loss may occur as part of an automatic migration, but for our series it is fine to keep it to true. - Overriding the Seed procedure which is used to seed our database with initial data, this procedure gets called every time our application starts, I’ve created a class called “
LearningDataSeeder” which is responsible to seed the database, I won’t list its code here but you can browse it on
GitHub or by downloading the source code for the API.
Till this point, we’ve implemented all the code needed to configure and create our eLearning database depending on model objects we’ve defined. We can stop at this point and consider our data layer completed, but we want to enhance it more and implement “Repository Pattern” which facilitates data access and manipulation once we start building the Web API. So in the next post, we’ll implement the Repository Pattern for our data access layer.
