Table of Contents
In the last article of learning entity framework, we learned about 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 a form of tutorial to set up basic Web API project and we’ll use code first approach of entity framework to generate database and perform CRUD operations. If you are new to Entity Framework, follow my previous articles on 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, 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 REST endpoints.
We'll follow a five-article series to learn the topic of entity framework in detail. All the articles will be tutorial form except the last where I'll cover the theory, history, use of entity framework. Following are the topics of the series.
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 provided 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 w.r.t. 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’s 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 code first approach out of those three in this tutorial.
Follow the steps mentioned below with images to create a web API 2 project.
- I am using Visual Studio 2017 for this tutorial. Open Visual Studio and add a new project.
- 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.
- When you click OK, you’ll be prompted to choose the type of ASP.NET Web Application. Choose Web API and click OK.
- 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.
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.
- Right-click Models folder and add a new class. Name the class as “
Student
”.
- Make the class
public
and add two properties to the class, i.e., Id
and Name
. Id
will serve as a primary key for 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; }
}
}
- Rebuild the solution.
Let’s add a controller that will contain the database operations to create, update, read and delete over our model class.
- Right click the controller folder and add choose the option to add a new
controller
class.
- In the next prompt, choose the option to create a Web API 2 Controller with actions, using Entity Framework. Click on Add button.
- Next, choose the model we created, i.e.,
Student
model in the option of Model
class.
- 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.
- The name of the controller should be “
StudentsController
”. Click Add to finish.
- 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 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.
- 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();
public IQueryable<Student> GetStudents()
{
return db.Students;
}
[ResponseType(typeof(Student))]
public IHttpActionResult GetStudent(int id)
{
Student student = db.Students.Find(id);
if (student == null)
{
return NotFound();
}
return Ok(student);
}
[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);
}
[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);
}
[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;
}
}
}
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.
- Open Package Manager Console and select the default project as your WebAPI project. Type the command Enable-Migrations and press enter.
- Once the command is executed, it makes some changes to our solution. As 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 folder. 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:
- 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" }
);
}
}
}
- 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.
- 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.
- 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()
. 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 vice versa 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");
}
}
}
- Again in the package manager console, run the command “Update-Database”.
- 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 e.g., it will not run previously executed migrations and all the newly added migrations each time will be taken into 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.
- Once the command is successfully executed, it creates the database in your local database server and adds the corresponding connection string in 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.
Let’s see what we got in our database when the earlier command got successfully executed.
- Since we used the local database, we can open it by opening Server Explorer from the View tab in Visual Studio itself.
- Once 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.
- Open the
Students
table and see the initial data added to the table with three student names that we provided in the Seed
method.
- 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.
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.
- 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.
- Once Postman is opened. You can choose various options from it. I choose the first option to Create a basic request. And save the name of the request as
TestAPI
. We’ll do all the tests with this environment.
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.
- 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 student
s added.
This URL will point to GetStudents()
action of our controller and the URL is the of the routing mechanism defined in Route.config file. In GetStudents()
method, the .Students
returned that means all the students
database returned as IQueryable
.
private StudentManagementContext db = new StudentManagementContext();
public IQueryable<Student> GetStudents()
{
return db.Students;
}
- 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.
[ResponseType(typeof(Student))]
public IHttpActionResult GetStudent(int id)
{
Student student = db.Students.Find(id);
if (student == null)
{
return NotFound();
}
return Ok(student);
}
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.
[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
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 in 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 whether 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 entity framework knows that this entity needs to be updated and then save changes to commit the changes to the database.
[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
”.
The delete verb as the name suggests is used to perform delete operations in the database. For e.g., 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 student
s 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.
[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.
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.