CRUD using ASP.NET Core Web API and Entity Framework Core

Welcome Everyone, 

In this blog, We'll learn how to create a CRUD API using asp.net core web API and Entity framework core. Here we'll connect with a database to perform the Create, Read, Update, and Delete (CRUD) operations.  

We've already discussed how to create a Web API using scaffolding. In this blog, We'll use the existing Employee Management project used in the previous blog. There we've already created the Controller using scaffolding. Here we'll connect that to the database and perform CRUD operation on Employee. Check out the blog if you want to know How to create a Web API using Asp.net core step by step. 

Tools used to create a web API using ASP.NET Core and Entity Framework Core

• Microsoft Visual Studio 2022 17.8.3
• .NET 8.0 (From .NET 5.0, the term Core is dropped to emphasize the main implementation of .NET. It supports more features than .Net Core.)
• Microsoft SQL Server 2019

In the previous blog, we've already configured the project on Visual Studio. Now it's turn to create the Database in SQL Server. So, let's create.

Create SQL Server Database

There are several ways to create a database in SQL SERVER. Here we'll create the database using Visual Studio.

Open Up SQL Server Object Explorer. If it is not already open then click on View > SQL Server Object Explorer.

Right click on SQL Server node Under SQL SERVER Object Explorer and click on ADD SQL Server. It will open up a window to connect to the SQL Server instance. 

1: Add SQL Server Instance

Here We'll expand Local, which will list out all the SQL Server instance that are running on our local computer and hit Connect.

2. Login to SQL Server

Now Expand the Database Server Instance added recently. Right-click on the Databases folder and select Add New Database. Provide your database name. Here we've given EmployeesDB as our Database name.

3. SQL Server View. List all databases present.

Hurray! Database is created. We're one step closer to CRUD using Web API and EF Core.

Configure Entity Framework Core

What is an Entity Framework?

We've already discussed about "What is Web API" in the previous blog. But, we know nothing about Entity Framework. Let's understand that first.

Introduction

Entity Framework is an object-relational mapping developed by Microsoft for .NET Applications. It eliminates the use of database code used to work with data. It enables us to work with data using objects without worrying about the database code. Using entity framework we can create, maintain, and perform any operation at a higher level with less code than traditional applications.

In simple terms, Entity Framework helps us perform any operation on the database without using the SQL query. Let's see the magic.

Entity Framework Core is the modern cross-platform ORM specifically designed for .net core and latest versions.

Install Entity Framework Core for our project

First of all, we have to install the entity framework core. To install the Entity framework core, go to package manager TOOLS > NuGet Package Manager > Manage NuGet Packages For Solutions.

4. Open NuGet Package Manager

Install the following packages :

1. Microsoft.EntityFrameworkCore.Tools
2. Microsoft.EntityFrameworkCore.SqlServer - Since we're using SQL server as our database server, we've installed the Microsoft.EntityFrameworkCore.SqlServer Nuget package. If we use different database platform, we must install the respective NuGet Package for that platform.

5. Manage Packages for Solution.

Entity Framework Development Approaches

As we're using Entity Framework as our data access technology, we can use any of the two approaches below:

1. Database first approach: In this approach, we create the Tables first inside the Database and after that, we generate the columns using EF Core Scaffold-DbContext Command. It is used where the database schema already exists.
2. Code first approach: In this approach, we define model classes in the application, and EF core will update the database using the Add Migration and Update Database command, It'll update the database i.e. create the tables as defined in the models.

So, in this blog, we'll follow the Code First Approach. We've only created the Database but we've not created the Tables inside it. We don't need to manually create the Tables. Entity Framework will create the table for us based on our model class. 

In code first approach Entity Framework will create or update the database schema based on the model and context class. 

So, let's define our model and context class.

Create a Model

Here we'll create a folder named Models. Inside the model, we'll keep the application's business logic, validation logic, and database access code. 

We'll create a model class named as EmployeeModel inside the Models folder. Our main focus is to Create, Read, Update, and Delete Employee records. So, we'll add some properties inside our model class. 

These fields will be automatically created as columns into our Database table, when we'll add migration using EF Core.

6. Employee.cs file

Why do we use models?

Models are used to represent the data and behavior of an entity in a structured way. Inside the model, we can add validation rules, data mapping annotations, etc.

If we add validation rules inside the model for properties and assign an invalid value to a property, It will not allow us to add that value to that property.

Models encapsulate data (in the form of properties) and behavior (in the form of methods) related to a particular entity. This encapsulation ensures that the internal state of the model remains consistent and that access to that state is controlled through well-defined interfaces.

We can reuse the model in the entire application.

Here, EmpolyeeModel.cs is our model class. Here, the structure of model class will be same as the Database table structure, which we'll use to store the Employees data. In simple terms, We'll use this model to save and retrieve employee records from database with the help of database context class in EF Core.

In our model, we have added an [Key] annotation, which will identify the property Id as Primary Key. When we migrate the changes to the database, initially it will add all the columns with all the constraints that are defined for that column inside our model class. It always checks the difference between the model properties and database table columns. If any changes are found, it will apply them to the respective table.

Create DB Context Class

The model is created, which maps the entities and relationships that are defined in the model to a database table. But to interact with the database we need a DBContext class. Our model class will be associated with the DBContext class. Once we associate the DBContext to a model we can write and execute the query, bind the query results into entity objects, track the changes made to the object, etc.

Here is the recommended way to create a DBContext class.

We've to define a class that derives from DBContext and has to expose the DbSet properties. The DbSet properties will hold the collection of specific entities in a context.

7. DbContext File.

We can see that there is a  DbSet<EmployeeModel> property present in our DbContext file. This will represent the table inside our database. If the table is not present it will create the table with the name of the DbSet<T>. 

Here in our case, it is Employees. So, it will create a table named Employees inside our database when we apply the EF migrations. 

This DbSet<T> is the entry point to Database table data. Through the DbSet we can do all the CRUD operation into our respective tables inside database. If we have multiple tables inside a Database, we have to create multiple DbSet<T> properties, which will represent their respective table.

Adding a Database Connection String

We've to add the Database ConnectionString to our project, which will be used to connect to the database and execute operations such as inserting, updating, deleting, etc. with EF Core.

Add the ConnectionString in the appsettings.json file.

8. Add the db connection string in appSetting.json file

Note : To get the connection string from Visual Studio, right-click on the SQL Server instance where your database is created under SQL Server Object Explorer, select Properties, and copy the connection string value. Paste it into the appsettings.json file.

Register DbContext Class in Service Container

By registering the DbContext class, It'll allow Dependency Injection. It centralizes configuration, that ensures consistency in database operation across the application. It manages the lifecycle of the DbContext class. Asp.net core registers the DbContext as Scoped lifetime, which means new instance will be created per request.

We've registered the DbContext class as shown in below image. In our case the DbContext class name is EmployeeDBContext.

9. Register DbContext in program.cs file

Add Migration to Database

Our project configured to connect with the database. As we're using the Code First Approach in EF Core, we've to add the table schema to our database. EF Core migration will do that for us. So, let's start. 

Click on Tools > Package Manager Console. This will open up the package manager console shown below. There, provide the command Add Migration {migration name} to package manger. This will prepare the code to Create tables in the Database.

Note: Here we've provided the migration name as InitialEmployeeSchema as we're going to add the schema for first time. You can give any meaningful name. Migration will generate the code for the schema by that class name.

10. Prepare DB migration

We can see that a folder named Migrations has been created. Inside that a class name InitialEmployeeSchema is created. The class name will be same as the migration name. This class will contain all the necessary code to create a Table in our EmployeesDB Database.

Migration doesn't apply the changes to database. It generates the necessary code to update the database by matching the current model. In our case we've one model that is EmployeeModel. So, it must creates the code to add the properties inside that model as columns.

Now it's time to create the tables in our EmployeesDB database. Pass the command Update-Database to the package manager as shown below. It will create the tables inside EmployeesDB.

11. Update database using EF Core

Hurray! We can see that the table is created as Employees. It is same as our DbSet Property name Employees in EmployeeDBContext class. The columns present in Employees are defined in our EmployeeModel class.

12. Employees Table.

Implementing Repository Pattern for Data Access in our Web API

Directly accessing the DBContext class from controller is not a good approach. We should avoid that. So, Here We'll use Repository pattern.

What is Repository Patten?

To make our code more readable and maintainable. we'll use the repository pattern here. Repository pattern will create an abstraction layer between the business logic and data access layer. We shouldn't write all of our business logic inside a controller action method. In simple terms, We'll not call the data access layer directly from our Controller. Rather, we should create a separate class which will handle the business logic of the application.

Also, it is recommended that, the controller action method should not be fat. We should strive for skinny controller action method.

There are lots of advantages of Repository pattern. One of them is code resuablity, maintainablity and also Testing will be easier. We'll discuss more about Repository pattern separately. So let's implement the Repository pattern here.

First of all, we'll create a folder named Repository. There we'll add an Interface named IEmployeeService and a class named EmployeeService. 

13. IEmployeeService.cs file

14. EmployeeService class implements IEmployeeService interface.

In the IEmployeeService we've added all the method declarations that will be used in our CRUD application. And will define all the methods one by one with demo. Here we've already declared the methods in the interface as we know that we need only four methods for CRUD. If your business logic demands more interface methods, you can declare them during development. It depends on the business logic of the application, and how many methods you need.

In EmployeeService Class, we've registered the EmployeeDBContext. Now, all the DB Operation will happen through EmployeeService not directly through Controller. 

Also, for now we have implemented the IEmployeeService interface inside our EmployeeService class. All the methods inside the IEmployeeService interface are added in EmployeeService class. Currently, they are not defined. We'll define all of them one by one with demo.

Also, we've to inject the EmployeeService into the EmployeeController. 

Now, the time has came to function those API. So, we'll start with Creating a record into the database through the POST API. As we know, we don't have any records in our database tables. So, first of all we need to create them. That's why we'll go with creating the record first.

First of all we need to inject the IEmployeeService interface to EmployeeController constructor using dependency injection. Here EmployeeController will not directly depend on EmployeeService. That's why we use dependency injection to create loosely coupled components.

Register IEmployeeService interface to EmployeeService in service container.

Register the IEmployeeService in the service container in the program.cs file. This will resolve the service in the runtime and controller can use it for every request. Here, we've Added it's lifetime as Transient, it will create the service each time it will be requested form controller.

15. Registering the EmployeeService class in Program.cs file.

Now, inject the IEmployeeService interface inside the EmployeeController using constructor injection.

16. Dependency Injection (DI) in EmployeesController.

Integration of Controller and Service class for individual HTTP Methods

POST Method

Endpoint - POST/ api/Employees

The service method AddEmployee(EmployeeModel employee) present in EmployeeService is responsible for creating an employee record. 

The action method having HttpPost as HttpVerb in EmployeesController will call the above method to create an Employee record.

So, let's first define the AddEmployee(EmployeeModel employee) in EmployeeSevice class and call this method from POST action method from EmployeesController. Also, there will be slight changes in our action method definition as well.

Let's implement them.

  
    
  public async Task<employeemodel> AddEmployee(EmployeeModel employee)
    
 {
    
    var result = await employeeDBContext.Employees.AddAsync(employee);
    
    await employeeDBContext.SaveChangesAsync();
    return result.Entity;
    
 }
  

Here is the definition for the POST action method inside EmployeesController

  
    
 [HttpPost]
 public async Task<IActionResult> Post([FromBody] EmployeeModel employee)
    
 {
    
     var result = await _employeeService.AddEmployee(employee);
    
     return CreatedAtAction(nameof(Post), result);
    
 }
  

Now. let's run the project. Open the swagger UI.

We can see the list of API's for Employee. But for now, we've only implemented the POST /api/Employees.

So, let's test the endpoint by creating an employee record.

17. Create Employee record.

Here we've passed the employee details that are needed to create an employee. After clicking on the Execute button, it gives us the following response. 

18. POST /api/Employees response.

You can see the response status is 201 with the Employee response. That means the employee is created in DB. Hurray! We've created our first employee record. 

Now, let's verify that the Employee record is actually created in our Employees table in EmployeeDB.

Expand EmployeeDB and then select the Employees table in SQL Server Object Explorer. Then right-click and select View Data. It will list out all the data present in Employees table.

19. Employee record added to in Employees Table.

GET Method

Endpoint - GET api/employee/{id}

Now, the time has come to get the employee details we've just created. When we'll pass the employee id, it should return the employee details matching with the employee ID.

To get an employee details, the action method having route as HttpGet("{id}") will be responsible in the EmployeesController.

The service method GetEmployeeById having int id as method parameter is responsible for getting the data from DB and return it to controller.

Let's implement both the methods.

Here is the service method GetEmployeeById implementation.

  
    
 public async Task<EmployeeModel> GetEmployeeById(int id)
    
 {
    
     return await employeeDBContext.Employees.FindAsync(id);
    
 }
  

Controller Action method having route HttpGet("id") will now look like this

  
    
 [HttpGet("{id}")]
    
 public async Task<IActionResult> Get(int id)
    
 {
     var result = await _employeeService.GetEmployeeById(id);
    


    
     if(result == null)
    
         return NotFound();
    


    
     return Ok(result);
    
 }
  

Let's again run the project. Go to Swagger UI and pass the id of the employee that is recently created. In our case the employee id is 1.

20. GET /api/Employees/1 response.

We can see, it is returning the exact information for that employee id = 1, that we've recently created.

It will return 404 Not Found, if we pass an id that doesn't exists in the database. We only have one record in our database, so if we pass an non existing id, then it will return 404 StatusCode with response message Not Found. 

21. Not Found, when emploee record doesn't exists.

PUT Method 

Endpoint - PUT api/employee/{id}

The action method having route HttpPut("{id}") will be used for updating an employee record.

The Service method UpdateEmployee(int id, EmployeeModel employee) will be responsible for updating an employee record. Here is the implementation for both the methods.

  
    
public async Task<EmployeeModel> UpdateEmployee(int id, EmployeeModel employee)
    
{
    
    var employeeBId = await employeeDBContext.Employees.FindAsync(id);
    


    
    if (employeeBId == null)
    
        return null;
    
    employeeDBContext.Entry(employeeBId).CurrentValues.SetValues(employee);
    
    await employeeDBContext.SaveChangesAsync();
    
    return employee;
    
}
  

  
    
[HttpPut("{id}")]
    
public async Task<IActionResult> Put(int id, [FromBody] EmployeeModel employee)
    
{
    
    if(id != employee.Id)
    
        return BadRequest();
    


    
    var result = await _employeeService.UpdateEmployee(id, employee);
    


    
    if (result == null)
    
        return NotFound(result);
    


    
    return Ok(result);
    
}
  

Now let's run the project. We'll try to update the employee, that we've created. Let's see what happens. Below image, we've passed updated request by changing the Department from IT to HR. 

22. Update Employee record.

We can see the employee record is updated. Let's verify the employee details by fetching that employee record.

23. Verify updated Employee.

Hurray! That Employee record is updated. 

GET Method (Get all Employee records)

Endpoint - GET api/Employees

Now, we'll see how to retrieve all the employees that are present in our database. Controller method having route as [HttpGet] without any route parameter will be called to get all the records.

Service method GetAllEmployees will retrieve all the employees and will return its to the Controller action method.

Here are both service and action method definitions for the above.

  
    
 public async Task<IEnumerable<EmployeeModel>> GetAllEmployees()
    
 {
    
     return await employeeDBContext.Employees.ToListAsync();
    
 }
  

  
    
 [HttpGet]
    
 public async Task<IActionResult> Get()
    
 {
    
     var results = await _employeeService.GetAllEmployees();
    
     return Ok(results);
    
 }
  

We've added some employees to our Employees table in the EmployeeDB database to verify that it is returning the list of all employees. Here is the list of employees that are added.

24. Manually added some Employee records.

Now, let's run the project. Execute the endpoint in swagger

25. GET /api/Employees response. Get all Employees records.

It is returning the list of all employees. Hurray! Our API is working fine.

DELETE Method

Endpoint -  DELETE api/Employee/{id}

Now, only deleting an employee record is left, The action method that has the route as [HttpDelete("{id}")] will be used to delete an employee record. So, let's implement that.

	
        public async Task<EmployeeModel> DeleteEmployee(int id)
        
{
        
    var employeeDetails = await GetEmployeeById(id);
        
    if (employeeDetails == null)
        
        return null;
        


        
    employeeDBContext.Employees.Remove(employeeDetails);
        
    await employeeDBContext.SaveChangesAsync();
        
    return employeeDetails;
        
}
	 

	
      
[HttpDelete("{id}")]
      
public async Task<IActionResult> Delete(int id)
      
{
    var result = await _employeeService.DeleteEmployee(id);
      
    if (result == null)
      
        return NotFound();
      


      
    return Ok();
      
}
	

It's time to execute. Run the project and project and pass an id to DELETE api/Employee/{id}. We've pass id as 3.

26. DELETE /api/Employee/3 response.

We can see that the response status code is 200. Which indicates that the employee record having id as 3 has been deleted.

Let's verify it is actually deleted. Execute the GET api/Employee. It'll return all the employees present in the database. Here is that.

27. List all employees after delete.

It is verified that the employee record with id = 3 is not present, Which means it is deleted.

Conclusion

Creating a web API using ASP.NET Core and Entity Framework Core offers a powerful and efficient way of building robust and scalable applications. Throughout this process, we have explored the fundamentals of ASP.NET Core and EF Core, including Repository pattern and integration with SQL Server. Now, using this approach, we as a developer, can design any type of Web APIs with the above tools. We can do much more with the ASP.NET Core Web API. Just stay tuned.

Share your thoughts and feedback regarding this article in comments.