Build a Complete CRUD API in .NET Web API Using Entity Framework Core

 

Introduction

In Day 13, we learned how to implement Global Exception Handling in .NET Web API using middleware, IExceptionHandler, and ProblemDetails.

Now, it's time to bring together the concepts we've learned throughout this series and build a practical CRUD API using .NET Web API, Entity Framework Core, and SQL Server.

CRUD is one of the most fundamental concepts in application development. Almost every business application needs to create, retrieve, update, and delete records.

In this article, we'll build an Employee Management API and implement all four CRUD operations step by step.

1. What Is CRUD?

CRUD stands for four basic database operations:

Operation	HTTP Method	Description
Create	POST	Insert a new record
Read	GET	Retrieve existing records
Update	PUT	Update an existing record
Delete	DELETE	Remove a record

Let's understand these operations using an Employee Management example.

• Create: Add a new employee.

• Read: Get all employees or a particular employee.

• Update: Modify employee details.

• Delete: Remove an employee from the database.

2. Technologies We Will Use

For this practical example, we'll use:

• .NET Web API

• C#

• Entity Framework Core

• SQL Server

• Dependency Injection

• DTOs and Model Validation

• Swagger for API testing

We'll follow a layered structure to keep our application organized and maintainable.

3. Project Structure

Let's organize the application into the following folders:

EmployeeManagementAPI
│
├── Controllers
│   └── EmployeeController.cs
│
├── Data
│   └── ApplicationDbContext.cs
│
├── DTOs
│   ├── CreateEmployeeDto.cs
│   ├── UpdateEmployeeDto.cs
│   └── EmployeeResponseDto.cs
│
├── Entities
│   └── Employee.cs
│
├── Exceptions
│   ├── NotFoundException.cs
│   └── GlobalExceptionHandler.cs
│
├── Services
│   ├── IEmployeeService.cs
│   └── EmployeeService.cs
│
├── appsettings.json
└── Program.cs

This structure separates controllers, database configuration, entities, DTOs, and business logic.

4. Create the Employee Entity

First, create an Employee.cs file inside the Entities folder.

namespace EmployeeManagementAPI.Entities;

public class Employee
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string Email { get; set; } = string.Empty;

    public decimal Salary { get; set; }

    public string Department { get; set; } = string.Empty;
}

This class represents the Employee table in SQL Server.

Entity Framework Core maps the properties of this class to database columns.

5. Create DTOs

As we learned in Day 9, DTOs help us control the data exchanged between clients and our API.

We'll create three DTOs.

CreateEmployeeDto

using System.ComponentModel.DataAnnotations;

namespace EmployeeManagementAPI.DTOs;

public class CreateEmployeeDto
{
    [Required]
    [StringLength(100)]
    public string Name { get; set; } = string.Empty;

    [Required]
    [EmailAddress]
    public string Email { get; set; } = string.Empty;

    [Range(0.01, 10000000)]
    public decimal Salary { get; set; }

    [Required]
    [StringLength(100)]
    public string Department { get; set; } = string.Empty;
}

This DTO defines the data required when creating an employee.

UpdateEmployeeDto

using System.ComponentModel.DataAnnotations;

namespace EmployeeManagementAPI.DTOs;

public class UpdateEmployeeDto
{
    [Required]
    [StringLength(100)]
    public string Name { get; set; } = string.Empty;

    [Required]
    [EmailAddress]
    public string Email { get; set; } = string.Empty;

    [Range(0.01, 10000000)]
    public decimal Salary { get; set; }

    [Required]
    [StringLength(100)]
    public string Department { get; set; } = string.Empty;
}

This DTO defines the fields that can be updated.

EmployeeResponseDto

namespace EmployeeManagementAPI.DTOs;

public class EmployeeResponseDto
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string Email { get; set; } = string.Empty;

    public decimal Salary { get; set; }

    public string Department { get; set; } = string.Empty;
}

This DTO defines the employee information returned to API consumers.

6. Configure ApplicationDbContext

Create ApplicationDbContext.cs inside the Data folder.

using EmployeeManagementAPI.Entities;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagementAPI.Data;

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(
        DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public DbSet<Employee> Employees => Set<Employee>();
}

Here:

• DbContext represents a session with the database.

• DbSet<Employee> represents the collection of Employee entities.

• Entity Framework Core uses this class to communicate with SQL Server.

7. Configure SQL Server Connection

Open appsettings.json and add the connection string.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Replace YOUR_SERVER_NAME with your SQL Server instance name.

For local development, Windows authentication can be convenient. For production, use secure connection settings and manage credentials through an appropriate secrets configuration.

8. Create the Service Interface

Now create IEmployeeService.cs inside the Services folder.

using EmployeeManagementAPI.DTOs;

namespace EmployeeManagementAPI.Services;

public interface IEmployeeService
{
    Task<List<EmployeeResponseDto>> GetAllAsync();

    Task<EmployeeResponseDto> GetByIdAsync(int id);

    Task<EmployeeResponseDto> CreateAsync(
        CreateEmployeeDto dto);

    Task<EmployeeResponseDto> UpdateAsync(
        int id,
        UpdateEmployeeDto dto);

    Task DeleteAsync(int id);
}

The interface defines the operations our Employee Service must implement.

This also helps us follow abstraction and dependency injection principles.

9. Implement Employee Service

Create EmployeeService.cs inside the Services folder.

using EmployeeManagementAPI.Data;
using EmployeeManagementAPI.DTOs;
using EmployeeManagementAPI.Entities;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagementAPI.Services;

public class EmployeeService : IEmployeeService
{
    private readonly ApplicationDbContext _context;

    public EmployeeService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<List<EmployeeResponseDto>> GetAllAsync()
    {
        return await _context.Employees
            .AsNoTracking()
            .Select(e => new EmployeeResponseDto
            {
                Id = e.Id,
                Name = e.Name,
                Email = e.Email,
                Salary = e.Salary,
                Department = e.Department
            })
            .ToListAsync();
    }

    public async Task<EmployeeResponseDto> GetByIdAsync(int id)
    {
        var employee = await _context.Employees
            .AsNoTracking()
            .FirstOrDefaultAsync(e => e.Id == id);

        if (employee == null)
        {
            throw new NotFoundException(
                $"Employee with ID {id} was not found.");
        }

        return MapToResponse(employee);
    }

    public async Task<EmployeeResponseDto> CreateAsync(
        CreateEmployeeDto dto)
    {
        var employee = new Employee
        {
            Name = dto.Name,
            Email = dto.Email,
            Salary = dto.Salary,
            Department = dto.Department
        };

        _context.Employees.Add(employee);

        await _context.SaveChangesAsync();

        return MapToResponse(employee);
    }

    public async Task<EmployeeResponseDto> UpdateAsync(
        int id,
        UpdateEmployeeDto dto)
    {
        var employee = await _context.Employees
            .FirstOrDefaultAsync(e => e.Id == id);

        if (employee == null)
        {
            throw new NotFoundException(
                $"Employee with ID {id} was not found.");
        }

        employee.Name = dto.Name;
        employee.Email = dto.Email;
        employee.Salary = dto.Salary;
        employee.Department = dto.Department;

        await _context.SaveChangesAsync();

        return MapToResponse(employee);
    }

    public async Task DeleteAsync(int id)
    {
        var employee = await _context.Employees
            .FirstOrDefaultAsync(e => e.Id == id);

        if (employee == null)
        {
            throw new NotFoundException(
                $"Employee with ID {id} was not found.");
        }

        _context.Employees.Remove(employee);

        await _context.SaveChangesAsync();
    }

    private static EmployeeResponseDto MapToResponse(
        Employee employee)
    {
        return new EmployeeResponseDto
        {
            Id = employee.Id,
            Name = employee.Name,
            Email = employee.Email,
            Salary = employee.Salary,
            Department = employee.Department
        };
    }
}

Let's understand the important methods:

GetAllAsync()

Retrieves all employees from the database. We use AsNoTracking() because this operation only reads data.

GetByIdAsync()

Retrieves an employee using the employee ID. If the record does not exist, it throws a custom exception.

CreateAsync()

Creates a new Employee entity from the incoming DTO and saves it to SQL Server.

UpdateAsync()

Finds the existing employee, updates the properties, and saves the changes.

DeleteAsync()

Finds the employee and removes the record from the database.

MapToResponse()

Converts an Employee entity into an EmployeeResponseDto.

10. Create NotFoundException

As discussed in Day 13, we'll use a custom exception when an employee does not exist.

Create NotFoundException.cs inside the Exceptions folder.

namespace EmployeeManagementAPI.Exceptions;

public class NotFoundException : Exception
{
    public NotFoundException(string message)
        : base(message)
    {
    }
}

Make sure the EmployeeService imports this namespace.

using EmployeeManagementAPI.Exceptions;

Our global exception handler should already be registered as explained in Day 13.

11. Create Employee Controller

Now create EmployeeController.cs inside the Controllers folder.

using EmployeeManagementAPI.DTOs;
using EmployeeManagementAPI.Services;
using Microsoft.AspNetCore.Mvc;

namespace EmployeeManagementAPI.Controllers;

[ApiController]
[Route("api/[controller]")]
public class EmployeeController : ControllerBase
{
    private readonly IEmployeeService _employeeService;

    public EmployeeController(IEmployeeService employeeService)
    {
        _employeeService = employeeService;
    }

    // GET: api/employee
    [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        var employees = await _employeeService.GetAllAsync();

        return Ok(employees);
    }

    // GET: api/employee/1
    [HttpGet("{id:int}")]
    public async Task<IActionResult> GetById(int id)
    {
        var employee = await _employeeService.GetByIdAsync(id);

        return Ok(employee);
    }

    // POST: api/employee
    [HttpPost]
    public async Task<IActionResult> Create(
        CreateEmployeeDto dto)
    {
        var employee = await _employeeService.CreateAsync(dto);

        return CreatedAtAction(
            nameof(GetById),
            new { id = employee.Id },
            employee);
    }

    // PUT: api/employee/1
    [HttpPut("{id:int}")]
    public async Task<IActionResult> Update(
        int id,
        UpdateEmployeeDto dto)
    {
        var employee = await _employeeService.UpdateAsync(
            id,
            dto);

        return Ok(employee);
    }

    // DELETE: api/employee/1
    [HttpDelete("{id:int}")]
    public async Task<IActionResult> Delete(int id)
    {
        await _employeeService.DeleteAsync(id);

        return NoContent();
    }
}

Let's understand the controller:

• [ApiController] enables API-specific behaviors, including automatic model validation responses.

• [Route("api/[controller]")] defines the route using the controller name.

• IEmployeeService is injected through the constructor.

• Each action delegates its work to the service.

• The controller returns appropriate HTTP responses.

Notice that the controller does not contain database queries. The database operations are handled by the service.

12. Configure Program.cs

Now register the required services and configure the middleware.

using EmployeeManagementAPI.Data;
using EmployeeManagementAPI.Exceptions;
using EmployeeManagementAPI.Services;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

builder.Services.AddEndpointsApiExplorer();

builder.Services.AddSwaggerGen();

// SQL Server configuration
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(
            "DefaultConnection")));

// Dependency Injection
builder.Services.AddScoped<
    IEmployeeService,
    EmployeeService>();

// Global Exception Handling
builder.Services.AddExceptionHandler<GlobalExceptionHandler>();

builder.Services.AddProblemDetails();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

// Middleware
app.UseExceptionHandler();

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Here, we have configured:

1. SQL Server using AddDbContext().

2. Dependency Injection using AddScoped().

3. Global Exception Handling using AddExceptionHandler().

4. Swagger for API documentation and testing.

5. Controller routing using MapControllers().

13. Create the Database Using Migrations

If you are using the Code First approach, execute these commands from the project directory.

Create the initial migration:

dotnet ef migrations add InitialCreate

Apply the migration:

dotnet ef database update

Entity Framework Core will create the database and the Employees table based on your entity and context configuration.

Make sure SQL Server is running and your application has the necessary database permissions.

14. Test CRUD Operations Using Swagger or Postman

Now let's test all four CRUD operations.

Operation 1: Create Employee

HTTP Method: POST

Endpoint:

POST /api/employee

Request Body:

{
  "name": "John Smith",
  "email": "john@example.com",
  "salary": 50000,
  "department": "IT"
}

Expected Response:

201 Created

The API creates a new employee and returns the created resource.

Operation 2: Get All Employees

HTTP Method: GET

Endpoint:

GET /api/employee

Expected Response:

[
  {
    "id": 1,
    "name": "John Smith",
    "email": "john@example.com",
    "salary": 50000,
    "department": "IT"
  }
]

This endpoint retrieves all employee records.

Operation 3: Get Employee By ID

HTTP Method: GET

Endpoint:

GET /api/employee/1

Expected Response:

200 OK

The API returns the employee whose ID is 1.

If the employee does not exist, the global exception handler returns a 404 Not Found response.

Operation 4: Update Employee

HTTP Method: PUT

Endpoint:

PUT /api/employee/1

Request Body:

{
  "name": "John Smith",
  "email": "john@example.com",
  "salary": 60000,
  "department": "Finance"
}

Expected Response:

200 OK

The employee's salary and department are updated.

Operation 5: Delete Employee

HTTP Method: DELETE

Endpoint:

DELETE /api/employee/1

Expected Response:

204 No Content

The employee record is removed from the database.

15. Understand the Complete Request Flow

Let's understand how a request travels through our application.

       Client
         |
         v
   EmployeeController
         |
         v
    IEmployeeService
         |
         v
    EmployeeService
         |
         v
 ApplicationDbContext
         |
         v
      SQL Server
         |
         v
    Database Result
         |
         v
   EmployeeService
         |
         v
 EmployeeResponseDto
         |
         v
   Controller
         |
         v
    HTTP Response
         |
         v
       Client

This layered architecture separates responsibilities:

• Controller: Handles HTTP requests and responses.

• Service: Contains application business logic.

• DbContext: Manages database communication.

• SQL Server: Stores application data.

• DTOs: Control the data exchanged with clients.

16. Important Best Practices

When building CRUD APIs for real-world applications, follow these practices:

1. Use DTOs instead of exposing database entities directly.

2. Keep business logic outside controllers.

3. Use asynchronous database methods such as ToListAsync() and SaveChangesAsync().

4. Use dependency injection to manage service dependencies.

5. Apply model validation to incoming requests.

6. Return appropriate HTTP status codes.

7. Use global exception handling for unhandled exceptions.

8. Use AsNoTracking() for read-only queries where appropriate.

9. Implement pagination when retrieving large datasets.

10. Add authentication and authorization to protect sensitive endpoints.

11. Avoid exposing sensitive database or exception information.

12. Add logging to troubleshoot application issues.

Conclusion

In this article, we built a complete Employee Management CRUD API using .NET Web API, Entity Framework Core, and SQL Server.

We implemented:

• Create Employee

• Get All Employees

• Get Employee By ID

• Update Employee

• Delete Employee

We also used DTOs, model validation, dependency injection, service-based architecture, SQL Server connectivity, and global exception handling.

These concepts form the foundation of building real-world business applications with .NET Web API.

What's Next?

Day 15 – Authentication and Authorization in .NET Web API

In the next article, we'll explore authentication and authorization, understand how they differ, and learn how to protect API endpoints so that only authorized users can access specific resources.

---

Skills Tutorials – Learn • Build • Grow Together

Continue learning with our 30-day .NET Core Web API series.