Hello There 👋🏻
We have a practical tutorial for today's article , which is , as you've guessed , CRUD operations using .Net web api and Entity Framework Core , this will be at a beginner level , I'll cover everything we come across , from setting up our domain models , configuring and adding our Db Context class , using migrations , to performing the CRUD operations , everything will be as simplified as possible so you can understand every concept effortlessly.

In the first article Getting started with Web APIs , I talked in depth about how APIs work , and we created one using the project template provided by Microsoft just to clarify what we've discussed. However , today we're mostly focusing on writing code , so if that sounds amusing to you , then let's get started.

We'll go through the following topics in this article:
📌1: CRUD overview and Theory.
📌2: Creating the project and cleaning up some boilerplate.
📌3: Downloading the required packages and tools.
📌4: Introduce our data model.
📌5: Setting up the Db Context class.
📌6: Create our database with migrations.
📌7: Implementing CRUD operations.
📌8: Testing all the endpoints.

We've got a lot to go over , so without further ado , let's get into it.

1: CRUD overview and Theory

Create , Read/Retrieve , Update and Delete are a set of operations which we can perform against a database , these 4 operations are the fundamentals of any database , every database should be able to handle them , let's look at each one individually:

1 Create: Creating a new record in a database table.

2 Read/Retrieve: Some people prefer Retrieve over Read , but it doesn't matter , but both mean to retrieve/read data from a database.

3 Update: Update an existing record in a database table.

4 Delete: Delete an existing record from a database table.

For today's tutorial , I'll be using SQL Lite , since it supports all the required operations , and is easier to setup.

2: Creating a new project and cleaning boilerplate code.

We'll use the Controller based API and not a Minimal API.

Open your terminal in the directory where you want to place your project , and type this command:

dotnet new webapi -n CRUDDemo 

This command will create a new web api project with the name CRUDDEMO , you can alter the named based off your needs.

After the project has been created , we'll clean it by getting rid of some auto-generated code.
Open the project in your preferred code editor , and delete the files highlighted below:

These 2 files are generated upon the creation of the project just to showcase how everything works.
So with that out of the way , onto the next point we go.

3: Downloading the required packages and tools

We'll need some packages to complete this tutorial , such as Entity Framework Core and a couple more , so again , back in the terminal , make sure we're inside the project files , type these commands one after the other , the order doesn't matter though !

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

These four packages are required in order for Entity Framework Core to work , you may also want to install the tool needed to run the migrations commands.

In your terminal , type this command:

dotnet ef

if the tool is already installed , you should get something similar to this :

If you didn't get as such , then run this command in your terminal to install the tool globally so you don't have to do it once more:

dotnet tool install dotnet-ef --global

The Global flag makes the tool accessible everywhere on your local machine.

Ok , we're done for this part , let's move on.

4: Introduce our data model

What's a data model you may ask , the answer is the following :
A data model is a representation of a real concept or object , in our case , it's going to be an Employee , keep in mind that the data model is context-driven , which means that the model may appear differently in a dissimilar context.

In the root directory of the project , create a new folder and name it Models , inside it , add a new class , call it Employee.cs , the employee class consists of the following properties :

public class Employee
{
    public int Id { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? Email { get; set; }
    public decimal Salary { get; set; }
}

This should do for our CRUD example.

NOTE: If a property named Id and is of type int , then Entity Framework Core will treat it as a unique ID which is controlled by Entity Framework , like when we add a new employee to our database , we shouldn't supply an Id , otherwise , Entity Framework will complain , because the Id will be automatically added , and we shouldn't provide one , however , you can use a different naming convention , like this:

public int EmployeeId {get; set;}

This will be treated similarly by Entity Framework to the one before.

5: Setting up the Db Context class

The Db Context class is our bridge to the database , through the context class , we can communicate with our database , so let's set it up and then add it to the Dependency Injection container or DI for short , so we can make use of it inside our application by injecting it through the constructor of any class where it's needed.

Create a new folder in the root by the name Data , inside it add a new class and it call AppDbContext.cs.
Assuming you downloaded the packages from step 3 , you should be able to import the entity framework core namespace , do it by adding the line to the top of the class file:
using Microsoft.EntityFrameworkCore;

Once the namespace is imported , make the AppDbContext inherit from DbContext , after doing this , create a constructor that takes a parameter of type DbContextOptions , this is a generic type , so you should replace the T with the name of the context class we created which is called AppDbContext and call the parameter options then pass it to the base class DbContext , the final result should look something like this:

Lastly , we need to make use of our Employee model , we'll use the code-first approach , which means the database table for employees will be generated according to our data model , the data model will represent a table , and each property of the model will be designated a field in that table , the primary key will be the ID property , which is the unique identifier that will never be repeated in the table more than once , we can achieve that by using the DbSet type that comes from the namespace we imported , so for now , add this line before or after the constructor of the context class:

public DbSet<Employee> Employees {get; set;}

Make sure to import the namespace for the Employee model , like this:
using CRUDDEMO.Models;

The context class is now ready , now our tour goes to appsettings.json , we need to add our connection string there , a connection string , put simply , is an expression that tells the application how to connect to a database by providing parameters inside it , don't worry though , our connection string will be extremely basic , change your appsettings.json to look like this:

NOTE! Make sure it's strings a not string.
DefaultConnection is the name of the connection , you can use whatever name you like , and the DataSource is called app.db , this is the name of our database that we'll be creating shortly , also this name is changeable , but add the extension .db.

Next stop 🚃: Program.cs
Now , we want to register the Db Context in the DI container , we'll do it quickly , just add the following code to Program.cs :

This does the registration for us , please ensure the namespaces in the top are imported , otherwise you'll get compile-time errors.

The point took so much time , hang tight , we still have a lot to do !

6: Create our database with migrations

Migrations allow us to evolve our database without having to write any database queries to create the database and its tables , that's why the code-first approach allows us to focus on writing code that will later handle the database creation and modifications.

In your terminal , run this command:

dotnet ef migrations add InitialCreate

The app should build successfully , if you peak at the file explorer , you'll see a new folder that's created automatically that goes by the name Migrations , inside it , we have auto-generated code that will handle the creation of the database and the tables , every time we want to evolve or alter our database , we would create a new migration.

Now , run this second command to apply the previously generated migrations:

dotnet ef database update

OUR DATABASE IS NOW SUCCESSFULLY GENERATED
Another file should appear in the explorer , called app.db , this is our database , you can use some software to view it , like dbeaver which is free , but I won't be covering that here.

7: Implementing CRUD operations

Now we get to the main coding parts , let's start quickly without too much talking.
Inside the controllers folder , create a new class and call it EmployeesController.cs
Copy and paste the following code to configure the controller:

using Microsoft.AspNetCore.Mvc;

namespace CRUDDEMO.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class EmployeesController : ControllerBase
    {
        public EmployeesController()
        {
        }
    }
}

Now add a new private readonly field of type AppDbContext and import the namespace for it , call it _context , like this:
private readonly AppDbContext _context;

After that , we'll use Dependency Injection to inject an instance of the context class through the constructor of our controller class and assign it to the private field we created shortly before , like the following:

private readonly AppDbContext _context;

public EmployeesController(AppDbContext context)
{
    _context = context;
}

ooo WE ARE READY TO START WORKING WITH THE DATABASE ooo

Let's create the first endpoint , I won't dive deeper in the creation of the endpoints since I covered that in the previous article of this series.

The Endpoints
1: GET all employees endpoint:

[HttpGet]
public async Task<IActionResult> GetAll()
{
    var employees = await _context.Employees.ToListAsync();

    return Ok(employees); //Return OK 200 Status Code 
}

This is a get endpoint , it gets all the employees from our database , and converts them to a list , and send them as a response.

NOTE! The ToListAsync(); is inside the Microsoft.EntityFrameworkCore namespace, so make sure to import it.

Now the second endpoint.

2: Get employee by Id
We need a different route for this endpoint , because we want an Id to be supplied in the route , the implementation is like the following:

[HttpGet("{id}")]
public async Task<IActionResult> GetById(int id)
{
    if (id == 0)
    {
        return BadRequest("0 is an invalid ID");
    }

    var existingEmployee = await _context.Employees.FindAsync(id);

    if (existingEmployee != null)
    {
        return Ok(existingEmployee); //Return 200 Ok Status Code.
    }
    else
    {
        return NotFound($"No employee was found with the ID: {id}"); //Return 404 Status Code
    }
}

The FindAsync method searches the table using the primary key , which is the Id in this case , if the employee doesn't exist , we return a not found with a message , if the given Id was 0 , return a bad request with an error message saying that 0 isn't valid.

2 DOWN , 3 MORE TO GO

3: Create new employee endpoint:
This endpoint will be for HttpPost requests, and will take an Employee from the body of the request as a parameter , let's code it:

[HttpPost]
public async Task<IActionResult> Create(Employee model)
{
    //Check the validity of the model.
    if (model == null)
    {
        return BadRequest("Please enter valid information");
    }
    else
    {
        await _context.Employees.AddAsync(model);
        await _context.SaveChangesAsync();

        return Ok(); //Return 200 OK Status Code.
    }
}

Ok there's something new here , that is the save changes part , this part is crucial , because when we add , delete or update something in the database , the changes stay in memory , they don't get persisted to the database , unless we call the save changes method , that's why we added this part there.

4: Delete an existing employee by its Id
This one is pretty simple , we retrieve the employee if they exist based on their Id , if they do , we remove them and save the changes , if they don't , we return a not found 404 status code , like this:

[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{
    if (id == 0)
    {
        return BadRequest("0 is an invalid ID");
    }

    var employeeToDelete = await _context.Employees.FindAsync(id);

    if (employeeToDelete != null)
    {
        _context.Employees.Remove(employeeToDelete);
        await _context.SaveChangesAsync();

        return Ok(); //Return 200 OK Status Code.
    }
    else
    {
        return NotFound($"No employee was found with the ID: {id}"); //Return 404 Status Code
    }
}

Pretty simple ay !?

Finally , the last endpoint we have

5: Update an existing employee endpoint
This one will handle HttpPut requests , again , we need to supply an Id, additionally , we also must supply an Employee model so we can use the received values to update an existing record in the database , the implementation is as follows:

[HttpPut("{id}")]
public async Task<IActionResult> Update(int id, Employee model)
{
    if (id == 0)
    {
        return BadRequest("0 is an invalid ID");
    }

    var employeeToUpdate = await _context.Employees.FindAsync(id);

    if (employeeToUpdate != null)
    {
        //Map the properties from the recieved model
        //to the model that we want to upadte.
        employeeToUpdate.FirstName = model.FirstName;
        employeeToUpdate.LastName = model.LastName;
        employeeToUpdate.Email = model.Email;
        employeeToUpdate.Salary = model.Salary;

        await _context.SaveChangesAsync();

        return NoContent(); //Return 200 OK Status Code.
    }
    else
    {
        return NotFound($"No employee was found with the ID: {id}"); //Return 404 Status Code
    }
}

Ok , that's it !

LET'S BEGIN TESTING

8: Testing the endpoints

Open Postman Or Insomnia , fire up the application , copy the URL , and let's get testing.

The URL should look something like this :
http://localhost:5078/api/employees

the port may be different , now if you send a get request to this URL , you should get a 200 ok as a response and an empty array of JSON Objects , because our database has no records in it , but that means everything is working , next up , a POST request , so we can create a new record , to do a post request , do the following in Postman:

Repeat that multiple times so the database gets populated a bit , then do a GET request so we can see all the records.

GET BY ID , after inserting some records , you can now select individual employees based on their Id .
Do a GET request like this:
http://localhost:5078/api/employees/1
This should get the first record in the database.

The DELETE works similarly , so does the update , but in the update , you must provide both an Id , and a model in the body.

And Ladies and Gentelmen , that's it for this article !

If you completed everything , then give yourself a pat on the back , it's really well deserved , because you've made significant progress and learned one of the most useful set of operations available out there , which you can then build upon by creating massively complex models with relationships and then use them to build more software applications in the latter future.

Hope You Enjoyed That As Much As I Did Writing It
And If You Learned Something New , Please Leave A Comment Down Below !

BYE FOR NOW !