ASP.NET Core 6: Creating API for CRUD Operations using EntityFrameworkCore (Basic for new ASP.NET Core Learners): Part 2

In Part 1, we have gone through the basic understanding of creating an ASP.NET Core Web API project. In this part, we will see how we can use EntityFramework Core (EF Core) to build Data Access Layer and create WEB API. 

EntityFramework Core

The EntityFramework Core is a cross-platform Object Relational Mapping (ORM). This provides an object model that manages database connections and mapping with tables of the database. The DbContext class provided in EF Core is used to manage database connections and performs commit transactions. The DbSet<T> generic in EF Core represent the entity class aka CLR Object mapping with a table of the database. Here we can say that T is an entity class mapped with the table of a database named T.  The EF Core provides the following two approaches:

  • Database First: The Entities and DataAccess layer is created based on the ready-to-use database
  • Code-First: The Database and its tables are created using entities we create in the project   

We can use EF Core with various databases like SQL Server, Oracle, MySQL, PostgreSQL, etc. In our application, we will use an SQL Server database with database first approach. The scripts of the database is provided in Part 1.  

In the .NET Core application, we can use EF Core using the following packages

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Relational
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Tools
Step 1: Open the command prompt and navigate to Core_EFApi project folder. On this path, we need to execute a command to install the package using the following command

dotnet add package [PACKAGE-NAME] -v [VERSION-NUMBER]

e.g. dotnet add package Microsoft.EntityFrameworkCore -v 6.0.13

Figure 1 shows all the packages installed in the project

Figure 1: The Installed Packages

Step 2: To use the database first approach, we must install the dotnet ef global tool using the following command executed from the command prompt

dotnet tool install --global dotnet-ef

Once the tool is installed, run the following command from the command prompt to generate entities from the database

dotnet ef dbcontext scaffold "[CONNECTION-STRING]" Microsoft.EntityFrameworkCore.SqlServer -o Models

Here the CONNECTION-STRING is connected to the database server and the -o Models switch is the folder where the entities will be generated. The example command is as follows

dotnet ef dbcontext scaffold "Data Source=.; Initial Catalog=Company;Integrated Security=SSPI" Microsoft.EntityFrameworkCore.SqlServer -o Models

Visit back to the project, now we can see the Models folder added in the project with class files for Department.cs, Employees.cs, and CompanyContext.cs. The Department.cs and Employees.cs files contain Department and Employee classes respectively, This is mapped with the respective tables in the Company database. The CompanyContext.cs class file contains CompanyContext class. This class is derived from DbContext class which managed Database connection using its OnConfiguring() method as well as the Table Mapping with Department and Employee class using DbSet properties declared in the class. The OnModelCreating() method is used to define the actual mapping and relationships across the mapped tables.

As shown in Figure 2, we will be registering the DbContext class as a service in the dependency container.

Figure 2: ASP.NET Core Services and Middlewares

We need to register the DbContext class in the DI container. Let's move the connection string from the OnConfiguring() method of the CompanyContext class and add it in appsettings.json as shown in Listing 1

 "ConnectionStrings": {
    "AppConnStr": "Data Source=.; Initial Catalog=Company;Integrated Security=SSPI"
Listing 1: The Connection string in the appsettings.json

Step 3: It is very important that we must not directly provide access to the DbContext to the API Controller class. Instead, we must create repository services to separate the Data access from the API controller class as shown in Figure 3

Figure 3: The Application Structure  

As shown in Figure 3, we must seperate the Data Access layer from API layer by creating the 'Service' Layer. The question here is what is it excatly? Simple answer of this question is, its a class(es) those contains code calling EF Core. We can implement these classes to contains CRUD operations for all mapped tables. Naturally, these classes will contains common code for CRUD operations but it is recommended that we have seperate class for each entity. We can implement this by creating a simple repositories.      

In the project, add new folder and name it 'Services'. In this folder, add new Interface file and name it as 'iService.cs'. This will be a generic interface containing asynchronous methods as shown in Listing 2

namespace Core_EFApi.Services
    public interface IService<TEntity, in TPk> where TEntity : class
        Task<IEnumerable<TEntity>> GetAsync();
        Task<TEntity> GetAsync(TPk id);
        Task<TEntity> CreateAsync(TEntity entity);
        Task<TEntity> UpdateAsync(TPk id, TEntity entity);
        Task DeleteAsync(TPk id); 

Listing 2: Service Interface 

The interface shown in listing 2 is multi-type generic interface. The TEntity is defined with constraints the it must be a class and TPk will always be an input generic parameter. Each method of this interface is an asynchronous method.

In the Services folder, add  a new class file and name it as DepartmentService.cs. In this call file we will add code as shown in Listing 3

using Core_EFApi.Models;
using Microsoft.EntityFrameworkCore;

namespace Core_EFApi.Services
    public class DepartmentService : IService<Department, int>
        CompanyContext ctx;

        public DepartmentService(CompanyContext ctx)
            this.ctx = ctx;

        async Task<Department> IService<Department, int>.CreateAsync(Department entity)
            var result = await ctx.Departments.AddAsync(entity);
            await ctx.SaveChangesAsync();
            return result.Entity;

        async Task IService<Department, int>.DeleteAsync(int id)
             var record = await ctx.Departments.FindAsync(id);
             if(record == null)
             await ctx.SaveChangesAsync();

        async Task<IEnumerable<Department>> IService<Department, int>.GetAsync()
            return await ctx.Departments.ToListAsync();

        async Task<Department> IService<Department, int>.GetAsync(int id)
            return await ctx.Departments.FindAsync(id); 

        async Task<Department> IService<Department, int>.UpdateAsync(int id, Department entity)
            var record = await ctx.Departments.FindAsync(id);
            if (record == null)
                return null;
            record.DeptName = entity.DeptName;
            record.Capacity = entity.Capacity;
            record.Location = entity.Location;
            await ctx.SaveChangesAsync();
            return record;

Listing 3: The DepartmentService class     

The code in Listign 3, show the DepartmentService class. This class is constructor injected with CompanyContext class. We will be registering the CompanyContext class in DI Container in later steps. The DepartmentService class implements IService interface. To this interface we have passed the TEntity type parameter as Department and TPk type parameter as int. All methods of this interface are implemented by the class will be performing CRUD operations for the Department Entity and all operations will take place on Department table of the Company database. Note that all operations are performed asynchronously this is recommended.

Similarly, we can create EmployeeService class inplementing IService inetrface for Employee entity. I leave the code creation for EmployeeService class on you. 
Step 4: Let's register the CompanyContext class and DepartmentService class in Dependency Container as services in Parogram.cs as shown in Listing 4
builder.Services.AddDbContext<CompanyContext>(options => 

builder.Services.AddScoped<IService<Department,int>, DepartmentService>();
Lisitng 4: The DI Registration of CompanyContext and DepartmentService class   

Code in listing 4, shows that the AddDbContext() method is used to register the CompanyContext class as service. This methods accepts DbContextOpetion class as input parameter. Using this parameter we can specify the database provider used by the application. In our case we are using UseSqlServer extension method because we are using SQL Server database.  The important point here to know that the CompanyContext will be registered as Scopped. This means that evrytime new CompnayContext instance will be created for each new session.   

We are also registering the DepartmentService class as a service as scoped registration. 

Step 5: Now we can add API controller class in the Controllers folder. Right-Click on the Controllers folder and select Add New Scaffolded Item options. This will show Add New Scaffolded Item window as shown in Figure 4, here we must select  API under Common section and then select API Controller-Empty as showin in Figure 4. Name this controller as DepartmentController.cs.

Figure 4: Adding new Controller
In the DepartmentConteoller, add the code as shoiwn in Lsiting 5. 

using Core_EFApi.Models;
using Core_EFApi.Services;
using Microsoft.AspNetCore.Mvc;

namespace Core_EFApi.Controllers
    public class DepartmentContreoller : ControllerBase
        private readonly IService<Department, int> deptServ;

        public DepartmentContreoller(IService<Department, int> serv)
            deptServ = serv;

        public async Task<IActionResult> Get()
            var response = await deptServ.GetAsync();
            return Ok(response);
        public async Task<IActionResult> Get(int id)
            var response = await deptServ.GetAsync(id);
            return Ok(response);
        public async Task<IActionResult> Post(Department department)
           var response = await deptServ.CreateAsync(department);
           return Ok(response);
        public async Task<IActionResult> Put(int id, Department department)
            var response = await deptServ.UpdateAsync(id, department);
            return Ok(response);
        public async Task<IActionResult> Delete(int id)
            await deptServ.DeleteAsync(id);
            return Ok("Record is Deleted");

Listing 5: The DepartmentController 

As shown in lisitng 5, the controller class is constructor injected with IService<Department,int> and hence it will be injected we an instance of the DepartmentService class which we have registered as a service as shown in lisitng 4. The API Controller class contains Get, Post, Put, and Delete methods decorated with the HttpGet, HttpPost, HttpPut, and HttpDelete attribute respectively. We have also defined Get() as overloaded method with one of the overloading accepts the id parameter. On this method we have applied HttpGet attribute with template paremeter passed to it. This will differenciate the Http Get request to these two overloaded methods. The Post, Put, and Delete methods will be invoked with corresponding Http request. 

Now lest run the applcation, we will see the Swagger page in browser where we can test API methods as shown in Figure 5

Figure 5: The Swagger UI

Click on Get button, it will show the Try it out button, click on this button ther Execute button will be shown , on clicking on Execute button  the data from teh Department table will be returned in JSON form as shown in Figure 6

Figure 6: The JSON data  

Similarly, click on the Post button, this will the schema for entering Department data as shown in Figure 7

Figure 7: The Post request 

Please note that, since Department contains collection of Employees, the relations is reflected here. To create new Department, modify this request by clicking Try it out button as shown in Figure 8

Figure 8: The Post request 

Click on Execute button, the response will generated as shown in Figure 9

Figure 9: The Post response      

Similarly, you can test Put and Delete requests. This is how ASP.NET Core Web API can be created. The inbuilt support for DI makes the application more maintainable. 

Futrther Reading

Custom Middlewares in ASP.NET Core

ASP.NET Core 6: Adding Custom Middleware and Logging the Error Message in Database (

Azure Active Directory Authentication

ASP.NET Core: How to implement Azure Active Directory (AAD) Authentication in ASP.NET Core (

ASP.NET Core 6 Localization

ASP.NET Core 6: Implementing a Localization in ASP.NET Core 6 (

ASP.NET Core 6 API Role Based Security with Token and Policies

ASP.NET Core 6: Using Role Based Security for ASP.NET Core 6 WEB API (

Model Binders

Using Model Binders in ASP.NET Core (

The code for this article can be downloaded from this link.


Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

ASP.NET Core 6: Downloading Files from the Server

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach