.NET 7 Web API CRUD Operation Using One-Many Relationship Tables

In this article, we will implement a CRUD Operation in Web API using one-many relationship tables.

Web API:

Web API is a framework for building HTTP services that can be accessed from any client like browser, mobile devices, and desktop apps.

In Simple terminology API(Application Programming Interface) means an interface module that contains a programing function that can be requested via HTTP calls either to fetch or update data for their respective clients.

Some of the key characteristics of API:

Support HTTP verbs like 'GET', 'POST', 'PUT', 'DELETE', etc.
Support default responses like 'XML' and 'JSON'. Also can define custom responses.
Supports self-hosting or individual hosting, so that all different kinds of apps can consume it.
Authentication and Authorization are easy to implement.

SQL Script For One-Many Relationship Tables:

For this demo, we are going to create sample tables like 'Customer' & 'CustomerAddresses'.

Customer Table:

CREATE TABLE Customer (
Id INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(200) NULL,
LastName VARCHAR(200) NULL,
Phone VARCHAR(10) NULL,
CONSTRAINT PK_Customer PRIMARY KEY (Id)
)

Here is our 'Customer' table script and 'Id' column is our primary key value.

CustomerAddresses Table:

Create Table CustomerAddresses(
Id int IDENTITY (1,1) NOT NULL,
City varchar(100),
Country varchar(100),
CustomerId int NOT NULL
CONSTRAINT PK_CustomerAddresses_Id PRIMARY KEY (Id)
CONSTRAINT FK_CustomerAddresses_CustomerId FOREIGN KEY (CustomerId)
REFERENCES Customer (Id)
)

Here is our 'CustomerAddresses' and 'Id' columns is our primary key, 'CustomerId' column is our foreign key.

Create .NET 7 Web API Application:

Let's create a .NET 7 Web API sample application to accomplish our demo. We can use either Visual Studio 2022 or Visual Studio Code(Using .NET CLI command) to create any .NET 7 application. For this demo, I'm using the 'Visual Studio Code' editor.

.NET CLI command to create API Project:

dotnet new webapi -o name_of_your_project

In our API project 'Program.cs' file is the entry file. So let's explore the default services and middleware configured in the 'Program.cs' file.

Services in 'Program.cs':

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

(Line: 1) The 'AddControllers' service for API controllers.
(Line: 2) The 'AddEndpointAPIExplorer' service gives support for minimal API.
(Line: 3) The 'AddSwagerGen' service for the swagger page

Middlewares in 'Program.cs':

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

(Lines: 3&4) The swagger middleware loads the swagger page.
(Line: 6) The 'UseHttpsRedirection' middleware redirects the 'HTTP request' to 'HTTPs'
(Line: 7) The user authorization middleware.
(Line: 8) The 'MapControllers' is an endpoint middleware that can point the route to the API controller.

SQL Connection String:

Let's prepare the SQL connection string.

Sample:

Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MyWorldDB;Integrated Security=True;Connect Timeout=30

Data Source - SQL server name.
Initial Catalog - Database name
Integrated Security - Windows authentication.
Connect Time - connection time out period

Now add the connection string into the 'appsettings.Development.json'.

appsettings.Development.json:

"ConnectionStrings": {
  "MyWorldDbConnection":"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MyWorldDB;Integrated Security=True;Connect Timeout=30"
}

Entity Framework Core:

Entity Framework Core is an Object/Relational Mapping(ORM) framework. EF Core makes database communication more fluent and easy. The 'DababaseContext' class acts as a database from our c# code, it will contain all registered classes 'DbSet<TEntity>'(TEntity is POCO class that represents the table).

Install Entity Framework Core NuGet Package:

Let's install the entity framework core NuGet package.

Visual Studio 2022:

Install-Package Microsoft.EntityFrameworkCore -Version 7.0.3

Visual Studio Code:

dotnet add package Microsoft.EntityFrameworkCore --version 7.0.3

Let's install the entity framework core SQL NuGet Package.

Visual Studio 2022:

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 7.0.3

Visual Studio Code:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 7.0.3

Create Entities With One To Many Relationships:

Let's create 'Customer' & 'CustomerAddresses' entities in the 'Data/Entites' folders(new folders).

Data/Entities/Customer.cs:

namespace Dot7.API._1ToMany.CRUD.Data.Entities;
public class Customer
{
    public int Id { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? Phone { get; set; }
    public List<CustomerAddresses> CustomerAddresses { get; set; }
}

Here 'CustomerAddresses' property is the navigation property. Here it represents one customer will have multiple customer addresses(one to many relationships).

Data/Entities/CustomerAddresses.cs:

namespace Dot7.API._1ToMany.CRUD.Data.Entities;
public class CustomerAddresses
{
    public int Id{get;set;}
    public string? City{get;set;}
    public string? Country{get;set;}
    public int CustomerId{get;set;}
    public Customer Customer{get;set;}
}

(Line: 7)The 'CustomerId' property will be our foreign key property.
(Line: 8) The 'Employee' is our navigation property.

Create DatabaseContext:

Let's create the DatabaseContext like 'MyWorldDbContext' in the Data folder.

Data/MyWorldDbContext.cs:

using Dot7.API._1ToMany.CRUD.Data.Entities;
using Microsoft.EntityFrameworkCore;
namespace Dot7.API._1ToMany.CRUD.Data;
public class MyWorldDbContext : DbContext
{
    public MyWorldDbContext(DbContextOptions<MyWorldDbContext> context) : base(context)
    {
    }
    public DbSet<Customer> Customer { get; set; }
    public DbSet<CustomerAddresses> customerAddresses { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CustomerAddresses>()
        .HasOne(_ => _.Customer)
        .WithMany(a => a.CustomerAddresses)
        .HasForeignKey(p => p.CustomerId);
    }
}

(Line: 4) The 'Microsoft.EntityFrameworkCore.DbContext' needs to be inherited by our 'MyWordDbContext' to act as a Database Context class.
(Line: 6) The 'Microsoft.EntityFrameworkDbContextOptions' is an instance of options that we are going to register in 'Program.cs' like 'database provider', 'connection string', etc.
(Lines: 9&10) All our table classes must be registered inside of our database context class with 'DbSet<T>' so that the entity framework can communicate with the table of the database.
(Line: 13-16) Using Ef Core fluent API we are defining our one-t-many relationship between the 'Customer' & 'CustomerAddresses' classes.

Now register our database context in the 'Program.cs'.

Program.cs:

builder.Services.AddDbContext<MyWorldDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("MyWorldDbConnection"));
});

Create A 'CustomerController':

Let's create a new controller like 'CustomerController.cs'.

Controller/CustomerController.cs:

using Dot7.API._1ToMany.CRUD.Data;
using Microsoft.AspNetCore.Mvc;
namespace Dot7.API._1ToMany.CRUD.Controllers;
[ApiController]
[Route("[controller]")]
public class CustomerController : ControllerBase
{
    private readonly MyWorldDbContext _myWorldDbContext;
    public CustomerController(MyWorldDbContext myWorldDbContext)
    {
        _myWorldDbContext = myWorldDbContext;
    }
}

(Line: 4) Decorated with the 'ApiController' attribute. It provides the API features to the controller, something like API versioning won't work if we don't use the attribute. So it is essential to configure for any controller.
(Line: 5) Route attribute defines the URL for API. The default expression '[Controller]' means the URL will contain the controller name(eg: 'Customer' will be used in the route). We can define the custom route that can be different from the controller name as well.
(Line: 6) To make our 'CustomerController' class an API controller it has to inherit the 'Microsoft.AspNetCore.Mvc.ControllerBase'.
(Line: 8-12) Injected our 'DatabaseContext'(MyWorldDbContext) into our controller constructor.

Implement Read Operation:

The implementing read operation means creating the HTTP GET endpoint.

So let's insert test data into the database as below.

Let's create an Action method(which is a logical unit of the controller) for HTTP GET requests. Inside this action method, we have to implement logic to fetch all data from the database.

Controllers/CustomerController.cs:

using Microsoft.EntityFrameworkCore;
[HttpGet]
public async Task<IActionResult> Get()
{
	var customers = await _myWorldDbContext.Customer
	.Include(_ => _.CustomerAddresses).ToListAsync();
	return Ok(customers);
}

Here we can observe there is no 'Route[]' attribute on the action method. If our controller contains a single HTTP GET action method then it is optional to add the 'Route[]' attribute. By convention, API will execute the action method based on the HTTP verb headers available for every request. But if we have multiple action methods of the HTTP verb type like 'GET', 'POST', 'PUT', and 'Delete' then we have to define the routes per action method explicitly.
(Line: 2) The 'HttpGet' attribute represents that our action method gets invoked for the HTTP GET request.
(Line: 3-8) Defined an asynchronous action method to return the collection of data.
(Line: 5-7) The 'Include' from the entity framework library is configured with a navigation property like 'CustomerAddresses'.The 'Include' generate a SQL Lef Join query between the 'Customer' & 'CustomerAddresses' table.

Now run the API and try to check our HTTP GET endpoint. We can face an error like 'A possible object cycle was detected' this is due to navigation properties between the 'Customer' & 'CustomerAddresses'.

So to resolve this, first let's install the below package.

Visual Studio 2022:

Install-Package Microsoft.AspNetCore.Mvc.NewtonsoftJson -Version 7.0.3

Visual Studio Code:

dotnet add package Microsoft.AspNetCore.Mvc.NewtonsoftJson --version 7.0.3

Now register the 'AddNewtonSoftJson' to the 'AddController' service in 'Program.cs'

Program.cs:

builder.Services.AddControllers() .AddNewtonsoftJson(options =>
    options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
);

Here we configure the circle object reference to be ignored.

Now run again and get access to our HTTP GET endpoint.

Implement Create Operation:

The implementing create operation means creating the HTTP POST endpoint.

Let's create Data Transfer Objects(DTOs) for our create operation like 'CustomerDto.cs' & 'CustomerAddressesDto.cs' in 'Dtos' folder(new folder). We can't use our table classes 'Customer.cs' & 'CustomAddresses.cs' because of the circular object reference. So we must create the DTOs.

Dtos/CustomerDto.cs:

namespace Dot7.API._1ToMany.CRUD.Dtos;
public class CustomerDto
{
    public int Id { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? Phone { get; set; }
    public List<CustomerAddressesDto> CustomerAddresses { get; set; }
}

Dtos/CustomerAddressesDto.cs:

namespace Dot7.API._1ToMany.CRUD.Dtos;
public class CustomerAddressesDto
{
    public int Id{get;set;}
    public string? City{get;set;}
    public string? Country{get;set;}
    public int CustomerId{get;set;}
}

Here in 'CustomerAddressDto' we didn't add the 'CustomerDTo' navigation property because to avoid the Circular reference object.

For easy mapping between 'Dto' & 'Entities'(table classes), we will use AutoMapper. So let's install the AutoMapper package.

Visual Studio 2022:

Install-Package AutoMapper.Extensions.Microsoft.DependencyInjection -Version 12.0.0

Visual Studio Code:

dotnet add package AutoMapper.Extensions.Microsoft.DependencyInjection --version 12.0.0

Now create a 'Profile' class like 'AppMapperProfile' to register the mappings.

AppMapperProfile.cs:

using AutoMapper;
using Dot7.API._1ToMany.CRUD.Data.Entities;
using Dot7.API._1ToMany.CRUD.Dtos;
namespace Dot7.API._1ToMany.CRUD;
public class AppMapperProfile:Profile
{
    public AppMapperProfile()
    {
        CreateMap<CustomerDto, Customer>();
        CreateMap<CustomerAddressesDto, CustomerAddresses>();
    }
}

(Line: 5) The 'AppMapperProfile' needs to inherit the 'AutoMapper.Profile'.
(Line: 9) Here registered 'CustomerDto' (as source object) and 'Customer' (as destination object).
(Line: 10) Here registered 'CustomerAddressesDto'(as source object) and 'CustomerAddresses' (as destination object).

Now register the AutoMapper service in the 'Program.cs'.

Program.cs:

builder.Services.AddAutoMapper(typeof(Program));

Let's create an Action method(which is a logical unit of the controller) for HTTP POST requests. Inside this action method, we have to implement logic to save a new record into the database.

Controllers/CustomerController.cs:

using AutoMapper;
using Dot7.API._1ToMany.CRUD.Data;
using Dot7.API._1ToMany.CRUD.Data.Entities;
using Dot7.API._1ToMany.CRUD.Dtos;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace Dot7.API._1ToMany.CRUD.Controllers;
[ApiController]
[Route("[controller]")]
public class CustomerController : ControllerBase
{
    private readonly MyWorldDbContext _myWorldDbContext;
    private readonly IMapper _mapper;
    public CustomerController(MyWorldDbContext myWorldDbContext, IMapper mapper)
    {
        _myWorldDbContext = myWorldDbContext;
        _mapper = mapper;
    }
    // some code hidden for display purpose
    [HttpGet]
    [Route("{id}")]
    public async Task<IActionResult> Get(int id)
    {
        var customerbyId = await _myWorldDbContext.Customer
        .Include(_ => _.CustomerAddresses).Where(_ => _.Id == id).FirstOrDefaultAsync();
        return Ok(customerbyId);
    }

    [HttpPost]
    public async Task<IActionResult> Post(CustomerDto customerPayload)
    {
         var newCustomer = _mapper.Map<Customer>(customerPayload);
        _myWorldDbContext.Customer.Add(newCustomer);
        await _myWorldDbContext.SaveChangesAsync();
        return Created($"/{newCustomer.Id}", newCustomer);
    }
}

(Line: 13-17) Injected the 'AutoMapper.IMapper' service into the constructor.
(Line: 29) The 'HttpPost' attribute represents our action method only gets executed for the HTTP POST request.
(Line:30) Here our action method gets payload of type 'CustomerDto'(this object contains collection of CustomerAddressDto data as well).
(Line: 31)Using automapper we converting payload type form 'CustomerDto' to 'Customer'(internal child object also will assigned that means 'CustomerAddressesDto' to 'CustomerAddress').
(Lines: 33&34) Trying to save data. Since 'Customer' entity has navigation property of collection 'CustomerAddress', so on saving parent data, child data also get inserted into the database.
(Line: 35) The 'Created()' method creates a 'CreatedResult' object that produces a status code of 201(created status code) as a response. The 'Created()' method first parameter will be the URL for endpoint where we can get item by is 'Id' value.
(Line: 20-27) Here is our second HTTP Get endpoint. This endpoint return single 'Customer' and multiple 'CustomerAddresses' as response. Since it is second HTTP endpoint we added the route attribute.

(Step 1)

(Step 2)

(Stpe 3)

(Step 4)

Implement Update Operation:

The implementing update operation means creating the HTTP PUT endpoint.

Let's create an Action method(which is a logical unit of the controller) for HTTP PUT requests. Inside this action method, we have to implement logic to update the item in the database.

Controllers/CustomerController.cs:

[HttpPut]
public async Task<IActionResult> Put(CustomerDto customerPayload)
{
	 var updateCustomer = _mapper.Map<Customer>(customerPayload);
	_myWorldDbContext.Customer.Update(updateCustomer);
	await _myWorldDbContext.SaveChangesAsync();
	return Ok(updateCustomer);
}

(Line: 1) The 'HttpPut' represents our action method that gets executed for only HTTP PUT requests.
(Line: 2-7) Asynchronous method for updating the records.
(Line: 4) Using auto mapper converting the 'CustomerDto' to 'Customer'.
(Line: 5) The 'Update()' method changes the database context state to 'Entity.Modified' which means data is ready to update. The 'Update()' method will update all columns of the record. If you try to pass only a few columns of data then the remaining columns get updated with their default values. One more thing to remember the payload should contain a primary key value(eg: 'Id' property).
(Line: 6) The 'SaveChangesAsync()' method updates the data in the database.

Implement Delete Operation:

The implementing delete operation means creating the HTTP DELETE endpoint.

Let's create an Action method(which is a logical unit of the controller) for HTTP Delete requests. Inside this action method, we have to implement logic to delete an item from the database.

Controllers/CustomerController.cs:

[Route("{id}")]
[HttpDelete]
public async Task<IActionResult> Delete(int id)
{
	var customerToDelete = await _myWorldDbContext.Customer
	.Include(_ => _.CustomerAddresses).Where(_ => _.Id == id).FirstOrDefaultAsync();
	if (customerToDelete == null)
	{
		return NotFound();
	}
	_myWorldDbContext.Customer.Remove(customerToDelete);
	await _myWorldDbContext.SaveChangesAsync();
	return NoContent();
}

(Line: 1) The '[Route]' attribute defined means 'id'(item id value we have to delete) value should be passed as part of URL, and the '{}' expression means any string defined inside of it will be the input parameter to the action method.
(Line: 2) The 'HttpDelete' attribute represents our action method getting invoked only for the HTTP Delete requests
(Line: 3-14) Asynchronous action method for deleting the record from the database.
(Line: 5)Fetching the record that needs to be deleted from the database. Here fetching 'CustomerAddresses' records also.
(Line: 11-12) Removing the record from the database context. The 'SaveChangesAsync()' update the database context to delete the record.

Support Me!
Buy Me A Coffee PayPal Me

Video Session:

Wrapping Up:

Hopefully, I think this article delivered some useful information on the .NET7 Web API CRUD using One-Many Relationship Tables. using I love to have your feedback, suggestions, and better techniques in the comment section below

Follow Me:

Comments

RaphaelApril 14, 2023 at 9:02 AM
Assuming a user can have more than one address. How can I add new address for a user?
ReplyDelete
Replies
AnonymousAugust 17, 2023 at 3:02 AM
Hello, thank you for you post. I test the code and the swagger POST always show "CustomerAddresses": null. I don't have CustomerAddresses in the Dto.
ReplyDelete
Replies

Add comment

Tech Seeker

Search This Blog