.NET 7 Web API CRUD Operation Using One-Many Relationship Tables
source link: https://www.learmoreseekmore.com/2023/03/dotnet7-webapi-crud-operation-using-ont-to-many-relationship-tables.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
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:
- 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:
- 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:
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
"ConnectionStrings": { "MyWorldDbConnection":"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MyWorldDB;Integrated Security=True;Connect Timeout=30" }
Entity Framework Core:
Install Entity Framework Core NuGet Package:
Create Entities With One To Many Relationships:
- 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:
- 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'.
- builder.Services.AddDbContext<MyWorldDbContext>(options =>
- options.UseSqlServer(builder.Configuration.GetConnectionString("MyWorldDbConnection"));
Create A 'CustomerController':
- 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:
- 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'.
- 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:
- 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.
- 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'.
- 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.
- 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)
Implement Update Operation:
- [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:
- [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:
Follow Me:
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK