Learning Web API 2 With Entity Framework 6 Code First Migrations

Introduction

In the last article of learning Entity Framework, we learned about the code-first approach and code-first migrations. In this article, we’ll learn how to perform CRUD operations with ASP.NET Web API2 and Entity Framework. We’ll go step by step in the form of a tutorial to set up a basic Web API project and we’ll use the code-first approach of Entity Framework to generate the database and perform CRUD operations. If you are new to Entity Framework, follow my previous articles explaining data access approaches with Entity Framework. The article would be less of a theory and more practical so that we get to know how to set up a Web API project using Entity Framework and perform CRUD operations. We’ll not create a client for this application but rather use Postman; i.e. ,the tool to test the REST endpoints.

Roadmap

We'll follow a five-article series to learn the topic of Entity Framework in detail. All the articles are in tutorial form except the last where I'll cover the theory, history, and use of Entity Framework. Following are the topics of the series.

• Learning Entity Framework (Day 1): Data Access Approaches of Entity Framework in .NET
• Learning Entity Framework (Day 2): Code First Migrations in Entity Framework in .NET
• Learning Entity Framework (Day 3): NET Web API 2 with Entity Framework 6 Code First Migrations
• Learning Entity Framework (Day 4): Code First Approach using Entity Framework Core in .NET
• Learning Entity Framework (Day 5): Entity Framework (The Theory)

Web API

I completely agree with the following excerpt from Microsoft documents.

“HTTP is not just for serving up web pages. HTTP is also a powerful platform for building APIs that expose services and data. HTTP is simple, flexible, and ubiquitous. Almost any platform that you can think of has an HTTP library, so HTTP services can reach a broad range of clients, including browsers, mobile devices, and traditional desktop applications.ASP.NET Web API is a framework for building web APIs on top of the .NET Framework.”

And there is a lot of theory you can read about Web API on MSDN.

Entity Framework

Microsoft Entity Framework is an ORM (Object-relational mapping). The definition from Wikipedia is very straightforward for ORM and petty much self-explanatory,

“Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. ”

Being an ORM, Entity Framework is a data access framework provided by Microsoft that helps to establish a relation between objects and data structure in the application. It is built over traditional ADO.NET and acts as a wrapper over ADO.NET and is an enhancement over ADO.NET that provides data access in a more automated way thereby reducing a developer’s effort to struggle with connections, data readers or data sets. It is an abstraction over all those and is more powerful with the offerings it makes. A developer can have more control over what data he needs, in which form and how much. A developer having no database development background can leverage Entity Framework along with LINQ capabilities to write an optimized query to perform DB operations. The SQL or DB query execution would be handled by Entity Framework in the background and it will take care of all the transactions and concurrency issues that may occur. Entity Framework offers three approaches for database access and we’ll use the code first approach out of those three in this tutorial.

 

Creating a Web API Project

Follow the steps mentioned below with images to create a Web API 2 project.

1. I am using Visual Studio 2017 for this tutorial. Open the Visual Studio and add a new project.
   
   

2. Choose the “Web” option in installed templates and choose “ASP.NET Web Application (.NET Framework)”. Change the name of the solution and project for e.g. Project name could be “StudentManagement” and Solution name could be “WebAPI2WithEF”. Choose the framework as .NET Framework 4.6. Click OK.
   
   

3. When you click OK, you’ll be prompted to choose the type of ASP.NET Web Application. Choose Web API and click OK.
   
   

4. Once you click OK, you’ll have default basic Web API project with required NuGet packages, files, and folders with Views and Controllers to run the application.
   
   

Creating the model

We’ll create a model class that will act as an entity for Student on which we need to perform database operations. We’ll keep it simple just for the sake of understanding on how it works. You could create multiple model classes and even can have a relationship between those.

1. Right-click Models folder and add a new class. Name the class as “Student”.
   
   

2. Make the class public and add two properties to the class, i.e., Id and Name. Id will serve as a primary key to this entity.
   
   

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   
   namespace StudentManagement.Models
   {
       public class Student
       {
           public int Id { get; set; }
           public string Name { get; set; }
       }
   }

3. Rebuild the solution.
   
   

Adding the API Controller

Let’s add a controller that will contain the database operations to create, update, read and delete over our model class.

1. Right click the controller folder and add choose the option to add a new controller class.
   
   

2. In the next prompt, choose the option to create a Web API 2 Controller with actions, using Entity Framework. Click on Add button.
   
   

3. Next, choose the model we created i.e. Student model in the option of Model class.
   
   

4. Since we do not have data context for our application, click on the + button close to Data context class option dropdown, and provide the name “StudentManagementContext” in the text box shown and click Add.
   
   

5. The name of the controller should be “StudentsController”. Click Add to finish.
   
   

6. Once you click "Add" to finish, it will try to create a scaffolding template of the controller with all read/write actions using Entity Framework and our model class. This will also add the reference to the Entity Framework and related NuGet packages because it is smart enough to understand that we want our controller to have database operations using Entity Framework as we mentioned the same in the second step on adding a controller. Creating scaffolding template may take a while.
   
   

7. Once the template is generated, you can see the controller class added to the Controller folder in the Web API project. This controller class derives from ApiController class and has all the methods that may be needed for performing a database operation on the student entity. If we check the method names, those are prefixed with the name of the verb for which the method is intended to perform an action. That is the way the end request is mapped to the actions. If you do not want your actions to be prefixed with the HTTP verbs, you can decorate your methods with HTTP verb attributes, placing the attribute over the method or applying attribute routing over the actions. We’ll not discuss those in details and will stick to this implementation.
   
   
   
   Controller class code
   

   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.Data.Entity;
   using System.Data.Entity.Infrastructure;
   using System.Linq;
   using System.Net;
   using System.Net.Http;
   using System.Web.Http;
   using System.Web.Http.Description;
   using StudentManagement.Models;
   
   namespace StudentManagement.Controllers
   {
       public class StudentsController : ApiController
       {
           private StudentManagementContext db = new StudentManagementContext();
   
           // GET: api/Students
           public IQueryable<Student> GetStudents()
           {
               return db.Students;
           }
   
           // GET: api/Students/5
           [ResponseType(typeof(Student))]
           public IHttpActionResult GetStudent(int id)
           {
               Student student = db.Students.Find(id);
               if (student == null)
               {
                   return NotFound();
               }
   
               return Ok(student);
           }
   
           // PUT: api/Students/5
           [ResponseType(typeof(void))]
           public IHttpActionResult PutStudent(int id, Student student)
           {
               if (!ModelState.IsValid)
               {
                   return BadRequest(ModelState);
               }
   
               if (id != student.Id)
               {
                   return BadRequest();
               }
   
               db.Entry(student).State = EntityState.Modified;
   
               try
               {
                   db.SaveChanges();
               }
               catch (DbUpdateConcurrencyException)
               {
                   if (!StudentExists(id))
                   {
                       return NotFound();
                   }
                   else
                   {
                       throw;
                   }
               }
   
               return StatusCode(HttpStatusCode.NoContent);
           }
   
           // POST: api/Students
           [ResponseType(typeof(Student))]
           public IHttpActionResult PostStudent(Student student)
           {
               if (!ModelState.IsValid)
               {
                   return BadRequest(ModelState);
               }
   
               db.Students.Add(student);
               db.SaveChanges();
   
               return CreatedAtRoute("DefaultApi", new { id = student.Id }, student);
           }
   
           // DELETE: api/Students/5
           [ResponseType(typeof(Student))]
           public IHttpActionResult DeleteStudent(int id)
           {
               Student student = db.Students.Find(id);
               if (student == null)
               {
                   return NotFound();
               }
   
               db.Students.Remove(student);
               db.SaveChanges();
   
               return Ok(student);
           }
   
           protected override void Dispose(bool disposing)
           {
               if (disposing)
               {
                   db.Dispose();
               }
               base.Dispose(disposing);
           }
   
           private bool StudentExists(int id)
           {
               return db.Students.Count(e => e.Id == id) > 0;
           }
       }
   }

Entity Framework Code First Migrations

Imagine a scenario where you want to add a new model/entity and you do not want the existing database to get deleted or changed when you update the database with the newly added model class. Code first migrations here help you to update the existing database with your newly added model classes and your existing database remains intact with the existing data. So, the data and the schema won’t be created again. It is a code first approach and we’ll see how we can enable this in our application step by step.

1. Open Package Manager Console and select the default project as your WebAPI project. Type the command Enable-Migrations and press enter.
   
   

2. Once the command is executed, it does some changes to our solution. As a part of adding migrations, it creates a Migrations folder and adds a class file named ”Configuration.cs”. This class is derived from DbMigrationsConfiguration class. This class contains a Seed method having the parameter as the context class that we got generated in the Models Seed is an overridden method that means it contains a virtual method in a base class and a class driven from DbMigrationsConfiguration can override that and add custom functionality. We can utilize the Seed method to provide seed data or master data to the database if we want that when our database is created there should be some data in a few tables.
   
   
   
   DbMigrationsConfiguration class,
   
   
   
   

3. Let’s utilize this Seed method and add a few students in the Students model. I am adding three students named Allen, Kim, and Jane.
   
   
   
   Configuration Class
   

   using StudentManagement.Models;
   
   namespace StudentManagement.Migrations
   {
       using System;
       using System.Data.Entity;
       using System.Data.Entity.Migrations;
       using System.Linq;
   
       internal sealed class Configuration : DbMigrationsConfiguration<StudentManagement.Models.StudentManagementContext>
       {
           public Configuration()
           {
               AutomaticMigrationsEnabled = false;
           }
   
           protected override void Seed(StudentManagement.Models.StudentManagementContext context)
           {
               context.Students.AddOrUpdate(p => p.Id,
                   new Student { Name = "Allen" },
                   new Student { Name = "Kim" },
                   new Student { Name = "Jane" }
               );
   
           }
       }
   }

4. The context parameter is the instance of our context class that got generated while we were adding a controller. We provided the name as StudentManagementContext. This class derives from DbContext class. This context class takes care of DB schema and the DbSet properties of this class are basically the tables that we’ll have when our database will be created. It added Students as a DbSet property that returns our Student model/entity and would be directly mapped to the table that will be generated in the database.
   
   

5. The next step is to execute the command named “Add-Migrations”. In the package manager console, execute this command with a parameter of your choice that would be the name of our first migration. I call it ”Initial”. So, the command would be Add-Migrations Initial
   
   

6. Once the command is executed, it adds a new file with the name “Initial” prefixed with the date time stamp. It prefixes the date time stamp so that it could track the various migrations added during development and segregate between those. Open the file and we see the class named “Initial” deriving from DbMigration class. This class contains two methods that are overridden from DbMigration class i.e. the base class. The method names are Up() and Down().
   
   The Up method is executed to add all the initial configuration to the database and contains the create command in LINQ format. This helps to generate tables and all the modifications done over the model. Down command is the opposite of Up command. The code in the file is self-explanatory. The Up command here is having the code that creates the Students table and setting Id as its primary key. All this information is derived from the model and its changes.
   
   
   
   Initial Migration,
   

   namespace StudentManagement.Migrations
   {
       using System;
       using System.Data.Entity.Migrations;
   
       public partial class Initial : DbMigration
       {
           public override void Up()
           {
               CreateTable(
                   "dbo.Students",
                   c => new
                       {
                           Id = c.Int(nullable: false, identity: true),
                           Name = c.String(),
                       })
                   .PrimaryKey(t => t.Id);
   
           }
   
           public override void Down()
           {
               DropTable("dbo.Students");
           }
       }
   }

7. Again in the package manager console, run the command “Update-Database”.
   
   

8. This is the final command that creates the database and respective tables out of our context and model. It executes the Initial migration that we added and then runs the seed method from the configuration class. This command is smart enough to detect which migrations to run. For example it will not run previously executed migrations and all the newly added migrations each time will be taken in to account to be executed to update the database. It maintains this track as the database firstly created contains an additional table named __MigrationHistory that keeps track of all the migrations done.
   
   

9. On the command is successfully executed, it creates the database in your local database server and adds the corresponding connection string to the Web.Config file. The name of the connection string is the same as the name of our context class and that’s how the context class and connection strings are related.
   
   

Exploring the Generated Database

Let’s see what we got in our database when the earlier command got successfully executed.

1. Since we used the local database, we can open it by opening Server Explorer from the View tab in Visual Studio itself.
   
   

2. Once the Server Explorer is shown, we can find the StudentManagementContext database generated and it has two tables named Students and __MigrationHistory. Students table corresponds to our Student model in the code base and __MigrationsHistory table as I mentioned earlier is the auto-generated table that keeps track of the executed migrations.
   
   

3. Open the Students table and see the initial data added to the table with three student names that we provided in the Seed method.
   
   

4. Open the __MigrationsHistory table to see the row added for the executed migration with the context key and MigrationId, Migration Id added is the same as the Initial class file name that got generated when we added the migrations through package manager console.
   
   

Running the application and Setup Postman

We got our database ready and our application ready. It’s time to run the application. Press F5 to run the application from Visual Studio. Once the application is up, you’ll see the default home page view launched by the HomeController that was automatically present when we created the WebAPI project.

1. Setup Postman. If you already have postman application, directly launch it and if not, search for it on Google and install it. The postman will act as a client to our Web API endpoints and will help us in testing the endpoints.
   
   

2. Once Postman is opened you can choose various options from it. I choose the first option to Create a basic And save the name of the request as TestAPI. We’ll do all the tests with this environment.
   
   
   
   
   

Endpoints and Database operations

We’ll test our endpoints of the API. All the action methods of the StudentsController act as an endpoint thereby following the architectural style of REST.

While consuming an API an Http Request is sent and in return, a response is sent along with return data and an HTTP code. The HTTP Status Codes are important because they tell the consumer about what exactly happened to their request; a wrong HTTP code can confuse the consumer. A consumer should know (via a response) that its request has been taken care of or not, and if the response is not as expected, then the Status Code should tell the consumer where the problem is if it is a consumer level or at API level.

GET

1. While the application is running that means our service is up. In the Postman, make a GET request for students by invoking the URL http://localhost:58278/api/students. When we click the Send button, we see that we get the data returned from the database for all the students added.
   
   
   
   This URL will point to GetStudents() action of our controller and the URL is the outcome of the routing mechanism defined in Route.config file. In GetStudents() method the db.Students are returned that means all the students from the database returned as IQueryable.
   

   private StudentManagementContext db = new StudentManagementContext();
   
           // GET: api/Students
           public IQueryable<Student> GetStudents()
           {
               return db.Students;
           }

2. One can invoke the endpoint to get the details of a single student from the database by passing his ID.
   
   

The GetStudent(int id) method takes student id as a parameter and returns the student from the database with status code 200 and student entity. If not found the method returns “Not Found” response i.e. 404.

// GET: api/Students/5
[ResponseType(typeof(Student))]
public IHttpActionResult GetStudent(int id)
{
    Student student = db.Students.Find(id);
    if (student == null)
    {
        return NotFound();
    }

    return Ok(student);
}

POST

We can perform POST operation to add a new student to the database. To do that, in the Postman, select the HTTP verb as POST and URL as http://localhost:58278/api/students. During POST for creating the student, we need to provide student details which we want to add. So, provide the details in the JSON form, since we only have Id and Name of the student in Student entity, we’ll provide that. Providing the Id is not mandatory here as the Id generated for the new student will be generated at the time of creation of student in the database and doesn’t matter what Id you supply via request because Id is identity column in the database and would be incremented by 1 whenever a new entity is added. Provide the JSON for a new student under the Body section of the request.

Before sending the request, we also need to set the header information for the content type. So, add a new key in Headers section of the request with name “Content-Type” and value as “application/json”. There are more keys that you can set in the headers section based on need. For e.g., if we would have been using a secure API, we would need to pass the Authorization header information like the type of authorization and token. We are not using secure API here, so providing content type information will suffice. Set the header information and click on Send to invoke the request.

Once the request is made, it is routed to PostStudent(Student student) method in the controller that is expecting the Student entity as a parameter. It gets the entity that we passed in the Body section of the request. The property names for the JSON in the request should be the same as the property names in our entity. Once the Post method is executed, it creates the student in the database and sends back the id of the newly created student with the route information to access that student details.

// POST: api/Students
[ResponseType(typeof(Student))]
public IHttpActionResult PostStudent(Student student)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }

    db.Students.Add(student);
    db.SaveChanges();

    return CreatedAtRoute("DefaultApi", new { id = student.Id }, student);
}

After the POST method is executed, check the Students table in the database and we see a new Student with the name John got created.

PUT

Put HTTP verb is basically used to update the existing record in the database or any update operation that you need to perform. For e.g. if we need to update a record I the database, say student name “Akhil” to “Akhil Mittal”, we can perform PUT operation.

Select the HTTP verb as PUT in the request. In the URL, provide the Id of the student that you want to update and now in the body section, provide the details, such as the updated name of the student. In our case “Akhil Mittal”.

Set the Content-type header and send the request.

Once the request is sent, it is routed to mapped PutStudent() action method of the API controller which takes id and student entity parameter. The method first checks that are the model passed is valid?, if not it returns HTTP code 400 i.e. Bad request. If the model is valid, it matches the id passed in the model with the student id and if they do not match, it again sends the bad request. If model and id are fine, it changes the state of the model to be modified so that the Entity Framework knows that this entity needs to be updated and then save changes to commit the changes to the database.

// PUT: api/Students/5
[ResponseType(typeof(void))]
public IHttpActionResult PutStudent(int id, Student student)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }

    if (id != student.Id)
    {
        return BadRequest();
    }

    db.Entry(student).State = EntityState.Modified;

    try
    {
        db.SaveChanges();
    }
    catch (DbUpdateConcurrencyException)
    {
        if (!StudentExists(id))
        {
            return NotFound();
        }
        else
        {
            throw;
        }
    }

    return StatusCode(HttpStatusCode.NoContent);
}

Check the database and the student name with id 4 is now updated to “Akhil Mittal”. Earlier it was “Akhil”.

DELETE

The delete verb as the name suggests is used to perform delete operations in the database. For example, if we need to delete a record in the database, like deleting the student “John” from the database, we can make use of this HTTP verb.

Set the HTTP verb as DELETE in the request and pass the student id that needs to be deleted in the URL for e.g. 5 to delete “John”. Set the content type and send the request.

The request is automatically routed to the DeleteStudent() action method of the API controller due to the name of the action. The method takes an id parameter to delete the student. The method first performs the get operation for the student with the id passed. If a student is not found, it sends back the error NotFound() i.e. 404. If the student is found, it removes the found student from the list of all students and then saves changes to commit the changes to the database. It returns OK; i.e., 200 status code in the response after a successful transaction.

// DELETE: api/Students/5
[ResponseType(typeof(Student))]
public IHttpActionResult DeleteStudent(int id)
{
    Student student = db.Students.Find(id);
    if (student == null)
    {
        return NotFound();
    }

    db.Students.Remove(student);
    db.SaveChanges();

    return Ok(student);
}

Check the database and we see the student with id 5 is deleted.

So, our delete operation also worked fine as expected.

Conclusion

In this article, we learned how to create a basic Web API project in Visual Studio and how to write basic CRUD operations with the help of Entity framework. The concept could be utilized in big enterprise level applications where you can make use of other Web API features like content negotiation, filtering, attribute routing, exception handling, security, and logging. On the other hand, one can leverage the entity framework’s features like various other approaches to data access, loadings, etc. Download the complete free eBook (Diving into Microsoft .NET Entity Framework) on Entity Framework here.