Developers Field: CRUD Operation With .NET Core 3.1 And Entity Framework Core

In this article I am going to walk you through CRUD operation using .net core 3.1 and entity framework core and SQL Server to store data. Here I will explain step by step how to Create, Read, Update and Delete employees from sql table using Rest API, EntityFramework core and sql server.

Before starting this there are a few prerequisites as below,

Visual Studio 2019
SQL server 2017
.Net core 3.1
Entity Framework core
PostMan to test rest api.

Let's start with Visual Studio to create Rest API endpoint for Create, Read, Update and Delete method using core api 3.1.

Start Visual Studio 2019 and click create a new project from the below screen.

Once you click on a new project you will get a new screen to select Asp .Net Core Web Application from the below screen and click on next button.

After clicking on the next button give a project name and location for your project where you want to save your project.

Now we have the option to select framework and framework version as in the below screen, I have selected core and version core 3.1.

After selecting the above option click on create. You are done with you new project for Rest API Core 3.1.

Once you are done with new core api project, you will get default controller with Weatherforcast as below.

Once you build your application then you should get the below screen by default. If you are getting the below screen then your solution is up and running.

Now let's start with SQL server script to create table in database.
1. CREATE TABLE [dbo].[Employees](
2. [Id] [int] IDENTITY(1,1) NOT NULL,
3. [FirstName] [varchar](50) NOT NULL,
4. [LastName] [varchar](50) NOT NULL,
5. [Email] [varchar](50) NOT NULL,
6. [City] [varchar](50) NOT NULL,
7. [Salary] [int] NOT NULL,
8. PRIMARY KEY CLUSTERED
9. (
10. [Id] ASC
11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
12. ) ON [PRIMARY]

Now let's create new folder as Model and add new class with name Employee under Model folder,
1. using System.ComponentModel.DataAnnotations;
3. namespace RestAPICoreDemo.Model
4. {
6. public class Employee
7. {
8. [Key]
9. public int Id { get; set; }
10. public string FirstName { get; set; }
11. public string LastName { get; set; }
12. public string Email { get; set; }
13. public string City { get; set; }
14. public int Salary { get; set; }
16. }
17. }

Now add EntityFrameworkCore package from nuget package manger. Install packages for Entity framework core
Microsoft.EntityFrameworkCore.SqlServer

Let's creat EmployeeContext which is used to communicate with database and help us to do operations like Create, Read, Update and Delete operation for our table.
1. using Microsoft.EntityFrameworkCore;
2. using RestAPICoreDemo.Model;
4. namespace RestAPICoreDemo
5. {
6. public class EmployeeContext : DbContext
7. {
8. public EmployeeContext(DbContextOptions<EmployeeContext> options) : base(options)
9. {
11. }
13. public DbSet<Employee> Employees { get; set; }
14. }
15. }

Let's create Employee service to perform all operation like Create, Read, Update and Delete operations. Add Service contract using interface and implement same interface using employee service.

Add service contract in Employee service interface

IEmployeeService.cs

using RestAPICoreDemo.Model;  
using System.Collections.Generic;  
  
namespace RestAPICoreDemo.Service  
{  
    public interface IEmployeeService  
    {  
  
        Employee AddEmployee(Employee employee);  
  
        List<Employee> GetEmployees();  
  
        void UpdateEmployee(Employee employee);  
  
        void DeleteEmployee(int Id);  
  
        Employee GetEmployee(int Id);  
    }  
}  

EmployeeService.cs

using RestAPICoreDemo.Model;  
using System.Collections.Generic;  
using System.Linq;  
  
namespace RestAPICoreDemo.Service  
{  
    public class EmployeeService : IEmployeeService  
    {  
        public EmployeeContext _employeeDbContext;  
        public EmployeeService(EmployeeContext employeeDbContext)  
        {  
            _employeeDbContext = employeeDbContext;  
        }  
        public Employee AddEmployee(Employee employee)  
        {  
            _employeeDbContext.Employees.Add(employee);  
            _employeeDbContext.SaveChanges();  
            return employee;  
        }  
        public List<Employee> GetEmployees()  
        {  
            return _employeeDbContext.Employees.ToList();  
        }  
  
        public void UpdateEmployee(Employee employee)  
        {  
            _employeeDbContext.Employees.Update(employee);  
            _employeeDbContext.SaveChanges();  
        }  
  
        public void DeleteEmployee(int Id)  
        {  
            var employee = _employeeDbContext.Employees.FirstOrDefault(x => x.Id == Id);  
            if (employee != null)  
            {  
                _employeeDbContext.Remove(employee);  
                _employeeDbContext.SaveChanges();  
            }  
        }  
  
        public Employee GetEmployee(int Id)  
        {  
            return _employeeDbContext.Employees.FirstOrDefault(x => x.Id == Id);  
        }  
  
    }  
}  

EmployeeController

using System.Collections.Generic;  
using Microsoft.AspNetCore.Mvc;  
using RestAPICoreDemo.Model;  
using RestAPICoreDemo.Service;  
  
namespace RestAPICoreDemo.Controllers  
{  
    [Route("api/[controller]")]  
    [ApiController]  
    public class EmployeeController : ControllerBase  
    {  
        private readonly IEmployeeService _employeeService;  
        public EmployeeController(IEmployeeService employeeService)  
        {  
            _employeeService = employeeService;  
        }  
  
        [HttpGet]  
        [Route("[action]")]  
        [Route("api/Employee/GetEmployees")]  
        public IEnumerable<Employee> GetEmployees()  
        {  
            return _employeeService.GetEmployees();  
        }  
  
        [HttpPost]  
        [Route("[action]")]  
        [Route("api/Employee/AddEmployee")]  
        public IActionResult AddEmployee(Employee employee)  
        {  
            _employeeService.AddEmployee(employee);  
            return Ok();  
        }  
  
        [HttpPost]  
        [Route("[action]")]  
        [Route("api/Employee/UpdateEmployee")]  
        public IActionResult UpdateEmployee(Employee employee)  
        {  
            _employeeService.UpdateEmployee(employee);  
            return Ok();  
        }  
  
        [HttpDelete]  
        [Route("[action]")]  
        [Route("api/Employee/DeleteEmployee")]  
        public IActionResult DeleteEmployee(int id)  
        {  
            var existingEmployee = _employeeService.GetEmployee(id);  
            if (existingEmployee != null)  
            {  
                _employeeService.DeleteEmployee(existingEmployee.Id);  
                return Ok();  
            }  
            return NotFound($"Employee Not Found with ID : {existingEmployee.Id}");  
        }  
  
        [HttpGet]  
        [Route("GetEmployee")]  
        public Employee GetEmployee(int id)  
        {  
            return _employeeService.GetEmployee(id);  
        }  
  
    }  
}  

We are done with service contract and implementation for all methods.
Now need for dependency for employee service and Connection string in start up class file.
1. public void ConfigureServices(IServiceCollection services)
2. {
3. services.AddControllers();
4. services.AddDbContextPool<EmployeeContext>(options =>
5. options.UseSqlServer(Configuration.GetConnectionString("EmployeeDBContextConnectionString")));
6. services.AddScoped<IEmployeeService, EmployeeService>();
7. }

Now we are ready with all required changes, let's build and run our Web API solution.
Use Postman to test our API CRUD operation.
Let's check our SQL Table with select statement. Initially there will not be any records in Employee table.

Now open Postman and create new employee using "api/Employee/AddEmployee" end point.

Read Employee using "api/Employee/GetEmployees" end point.

Update Employee "api/Employee/UpdateEmployee" using end point.

Let's check updated employee using "api/Employee/GetEmployee" endpoint which should return employee details with updated data as below.

Delete Employee "api/Employee/DeleteEmployee" using end point which should retunr status code 200 with Ok. It means deleted employee from table.

Let's check the same employee using "api/Employee/GetEmployee" by passing employee id. Now it should return status code 204 No Content. It means employee is sucessfully deleted using delete endpoint.