This is the first part of Building ASP.Net Web API RESTful Service Series. The topics we’ll cover are:
- Building the Database Model using Entity Framework Code First – Part 1 (This Post).
- Applying the Repository Pattern for the Data Access Layer – Part 2.
- Getting started with ASP.Net Web API – Part 3.
- Implement Model Factory, Dependency Injection and Configuring Formatters – Part 4.
- Implement HTTP actions POST, PUT, and DELETE In Web API – Part 5.
- Implement Resources Association – Part 6.
- Implement Resources Pagination – Part 7.
- Securing Web API – Part 8.
- Preparing Web API for Versioning – Part 9.
- Different techniques to Implement Versioning – Part 10.
- Caching resources using CacheCow and ETag – Part 11.
Update (2014-March-5) Two new posts which cover ASP.Net Web API 2 new features:
Update (2014-April-16) New multi part series tutorial which covers building OData Service using ASP.Net Web API.
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 of 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 EntityFramework:
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
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 conventions 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 derives from System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<T>
Classes are: “CourseMapper”, “EnrollmentMapper”, “StudentMapper”, “SubjectMapper”, and “TutorMapper”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
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 configuration 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 the below:
- Each “Course” has a “Subject”.
- Each “Tutor” can tech multiple “Courses”.
- Each “Student” can enroll in multiple “Courses”. So we’ll have Many-to-Many table to persist the relation called “Enrollment”.
Step 5: Creating Context Class to Handle Database Persistence
Now we need to add new class named “LearningContext” which derives from class “System.Data.Entity.DbContext”:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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 they are:
- Exposing our POCO classes as public DbSet properties, this means that every POCO class is transferred to a database table.
- Overriding 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 they 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. new property has been added). To implement this we need to add new class called “LearningContextMigrationConfiguration” which derives from class “System.Data.Entity.Migrations.DbMigrationsConfiguration<TContext>”. The code listing as below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 they 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 responsible to seed the database, I won’t list it is 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.
Hi,
Kudos for your tutorial that has been very helpfull and a breeze to implement.
Could you explain the difference between this 2 lines. I m struggling to understand why the WithMany is called once with parameters and once without:
this.HasOptional(e => e.Student).WithMany(e => e.Enrollments).Map(s => s.MapKey(“StudentID”)).WillCascadeOnDelete(false);
this.HasRequired(c => c.CourseTutor).WithMany().Map(t => t.MapKey(“TutorID”));
What would be the impact on the database model created if i would use instead:
this.HasRequired(c => c.CourseTutor).WithMany(t=>t.Courses).Map(t => t.MapKey(“TutorID”));
Thaks
Hello Avi,
Based on MSDN the HasRequired Configures a required relationship from this entity type. Instances of the entity type will not be able to be saved to the database unless this relationship is specified. The foreign key in the database will be non-nullable.
hi,
the question is more on the WithMany called once with a lambda and once without…
Hi tjoudeh,
Nice work.:) Seems you missed stating about Enums class.
namespace Learning.Data.Enums
{
public enum Gender
{
Male = 0,
Female = 1
}
}
Everything else is clearly explained for every beginners. This is exactly what I was searching.
Thanks from my heart.:D
Hello Harris,
Glad it was beneficial for you. Good catch, I forgot to mention it, but it will function properly because it simple int enum.
Yeah Cheers 🙂
Hey I’m fairly new to asp.net and I tried both of your was but the project wouldn’t build. The way I did it was…
namespace Learning.Data.Entities
{
public class Enums
{
public enum Gender { male, female }
}
}
hopefully this helps someone in the future. Thanks for the great tutorial
Thanks Stan for your feedback, maybe I missed this in the description, better to fork the running project on GitHub and follow along with your version. Good luck.
Hi,first of all thanks for the tutorial.I’ved downloaded your complete code,but when i run it,it gives
Server Error in ‘/’ Application.
The resource cannot be found
How can i make it works?Thanks
You welcome, there is no home page so thats normal, did you try to make Http Get request to one of the resource like http://localhost:yourport/api/courses/ using fiddler?
I’m using firefox Poster addons,and it fails. It says : No response was received. Either the request could not be opened or the request timed out.
Sorry for the late reply but did it work with you using fiddler/postman?
Simple question, why are you using the mapper classes, wouldn’t annotations serve the same purpose without the need for the additional classes? To me that would also improve readability. Is there any reason not to use them? In other words, drop the Mapping class and change the Courses class to look like this :
[Table(“Courses”)]
public class Course
{
[Key]
public int Id { get; set; }
[Required]
[MaxLength(255),MinLength(5)]
…
public volatile ICollection Enrollments { get; set; }
}
Hello Terje, you can use data annotations on entities, but I prefer to use fluent API and configure relationships in separate classes. In my opinion keeping your classes real Plain Old C Objects without any attributes is a good practice and you do all your configuration and mapping in one place.
Hello Terje, Data annotations is subset of fluent API ,Everything what you can configure with DataAnnotations is also possible with the Fluent API. The reverse is not true.Data annotations is good for simple application .good luck.
Hello Md,
You are right, Keeping your POCO classes a real POCOs with no attributes is good practice, that’s why I prefer using fluent API.
Salem,
First of all I would like to thank you very very much for this perfect tuto and for your serious work. Thank you sir.
Now i am trying to rebuild this project but with “Code First Reverse Engineering existing Database”.
So did I still need ” class LearningContextMigrationConfiguration ” ?
Hello Mohamed, Glad you liked the tutorial, hopefully it will benefit you.
You do not need to use ContextMigration class if you want, this class about setting your initialization strategy and will be helpful for future updates on your DB model (POCO classes).
You can set it to Null or use another initialization strategy such as create data base if model doesn’t exist.
Hope this answers your question.
Thanks,
This is all what I needed
The good news that my solution until now work without erreur ( I am using my own existing DataBase)
Can you please tell me where can I ask you about authentication Web Api V2 ( I am using VS 2012 )
I have severe problem with it, maybe because all tuto that I found use VS 2013 and my Os don’t support it.
Lots of regard
May Allah bless you,
Hello Mohamed,
It is not about Visual Studio version, it is about Framework you are using (.NET 4, 4.5, or 4.5.1) and the version of your Web API assemblies, so what you want to know about authentication?
Hello,
What I have to do is to create ” login authentication service web with WEP API V2.
So how can I start, please.
Any advices …
and thank you a lot
Thanks for a great tutorial, I’m just starting out with this technology and have found it difficult to find an end-to-end tutorial that just works. This is excellent and explains things with just the right amount of detail.
One thing I can’t find is what I need to change to make this look at a different database. I can see EF has used my local SQLExpress instance by default as expected, but supposing the very same tables were in my production SQL Server 2008, how would I tell EF to look there instead?
Many thanks!
Hello Jeremy,
Glad you found this tutorial useful 🙂
Just include a valid connection string in your web.config and it should point to the server/database in this connection and it won’t point to your SQL express, let me know if this works for you.
Perfect, thank you!
excellent ! thanks for sharing !
Glad you liked it 🙂
Hello Taiseer. Thanks a bundle for this series, I love working with the way you injected the repository and model factory into a custom ApiController object that each controller could inherit from thus allowing easy access to these objects in a really easy way.
I was wondering if you would consider adding to this series with a section on unit testing and how to set it up using your favorite testing and mocking tools. I would love to see how it all comes together.
Hi Josh,
Thanks for taking the time to read the series and provide a feedback. I’m thinking to update this series to use Web API 2.2 and add the unit tests, it is one of the long to-do list items I’m planing to achieve :S hopefully I’ll have the time soon to work on this.
That would be fantastic, I’ll keep checking back, now and then, to see what you end up deciding. Thanks for the reply!
Thanks for this fantastic set !
I have a question though, I’m getting this Error :
The type or namespace name ‘Enums’ could not be found (are you missing a using directive or an assembly reference?)
any idea why ?
You are welcome, glad you liked it check this file on GitHub where I forgot to mention using it in the post. Simple Gender values for (0, and 1).
Hope this answers your question.
Great, solved my problem ! your site is a treasure that I just discovered and it seems that I’ll be here for a while 😉
Thanks, happy to help. Let me know if you have another questions.
hi Taiseer nice tutorial could you please explain this code what you used in your model
public Subject()
{
Courses = new List();
}
Hi Engr, this is constructor is used to initialize the Courses list to avoid null exceptions, I want the property to contain an empty list of courses where I’ll fill later on demand.
Wanted to mention one change I had to do with Entity Framework 6.1.3 Fluent API. I had to make the relationship as follows:
this.HasRequired(i => i.ItemOrder)
.WithMany(i => i.Items)
.Map(o => o.MapKey(“OrderId”));
If I didn’t include i => i.Items, EF ended up creating an extra (FK, nullable) column called Order_Id – which I didn’t want.
Yes, I am using “independent association” exactly like the samples here.
Hi Taiseer, another nice article I’m following to create architecture of my web api application. I decoupled authorization server and resource server. However both these servers point out same database and uses code first approach. Authorization server schema was created using aspnet identity and resource server I have my own schema.
Is there any way to manage same database with code first model from two different applications. I heard about contextkey support in ef6 which can be used to do so. Is there any other best practice available.
In my resource server I need AspNetUsers table to create foreign key relationship between user table and other tables. How should I do that, provided I don’t have AspNetUser POCO class on resource server.
hi TAISEER
why no one discussed the the relation between user table and other tables every record added in any table “Admin” must know about it and also date of added and date of updated , i can not find this approach in any tutorial although it required in real world .
hi Taiseer,
Thanks for great article!
i have question on models created from relational schema.
there is one to may relationship between tutor and Courses.
1 tutor can teach many courses –>which leads to –> tutor class will have list
And you have also added Tutor object in Courses class.
which means
TutorObj–>List–>TutorObj
However why do we need “Tutor Object” in Courses Class ? why not only “TutorId”
i think there will be circular dependency with this design.
Can you please explain if i am missing something