how to Insert data into header and line table with PK/FK relation at a time using .NET core API, Oracle21c (XE), postman

Project Structure:

Project Depedency:

Database Table: 

CREATE TABLE ItemHeader (
    HeaderId NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    HeaderName VARCHAR2(100),
    CreatedDate DATE DEFAULT SYSDATE
);

CREATE TABLE ItemLine (
    LineId NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Auto-increment
    HeaderId NUMBER,
    LineDescription VARCHAR2(200),
    Quantity NUMBER,
    FOREIGN KEY (HeaderId) REFERENCES ItemHeader(HeaderId)
);

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace itemdetails.Models
{
    public class ItemHeader
    {
        [Key]
        public int HEADERID { get; set; } // Primary Key
        public string HEADERNAME { get; set; }
        public DateTime CREATEDDATE { get; set; }
        public ICollection<ItemLine> ItemLines { get; set; } // Navigation property
    }
}

using Microsoft.AspNetCore.Mvc.ModelBinding.Validation;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json.Serialization;

namespace itemdetails.Models
{
    public class ItemLine
    {
        [Key]
        public int LINEID { get; set; } // Primary Key
        public int HEADERID { get; set; } // Foreign Key

        public string LINEDESCRIPTION { get; set; }
        public int QUANTITY { get; set; }

        [JsonIgnore] // Ignore during JSON deserialization
        public ItemHeader ItemHeader { get; set; } // Navigation property
    }
}

namespace itemdetails.Models
{
    public class ItemHeaderDto
    {
        public string HeaderName { get; set; }
        public DateTime CreatedDate { get; set; }
        public List<ItemLineDto> ItemLines { get; set; }
    }
}

namespace itemdetails.Models
{
    public class ItemLineDto
    {
        public string LineDescription { get; set; }
        public int Quantity { get; set; }
    }
}

using itemdetails.Models;

namespace itemdetails.Service
{
    public interface IItemService
    {
        Task<ItemHeader> CreateItemHeaderAsync(ItemHeader itemHeader);
        Task<ItemLine> CreateItemLineAsync(ItemLine itemLine);
        Task<ItemHeader> CreateItemHeaderWithLinesAsync (ItemHeader itemHeader);
    }
}

using itemdetails.Models;
using itemdetails.Repository;

namespace itemdetails.Service
{
    public class ItemService : IItemService
    {
        private readonly IItemRepository _itemRepository;
        public ItemService(IItemRepository itemRepository)
        {
            _itemRepository = itemRepository;
        }
        public Task<ItemHeader> CreateItemHeaderAsync(ItemHeader itemHeader)
        {
            throw new NotImplementedException();
        }      

        public Task<ItemLine> CreateItemLineAsync(ItemLine itemLine)
        {
            throw new NotImplementedException();
        }
        public Task<ItemHeader> CreateItemHeaderWithLinesAsync(ItemHeader itemHeader)
        {
            return _itemRepository.AddItemHeaderWithLinesAsync(itemHeader);
        }
    }
}

using itemdetails.Models;

namespace itemdetails.Repository
{
    public interface IItemRepository
    {
        Task<ItemHeader> AddItemHeaderAsync(ItemHeader itemHeader);    
        Task<ItemLine> AddItemLineAsync(ItemLine itemLine);
        Task<ItemHeader> AddItemHeaderWithLinesAsync(ItemHeader itemHeader);
    }
}

using itemdetails.Context;
using itemdetails.Models;
using Microsoft.EntityFrameworkCore;

namespace itemdetails.Repository
{
    public class ItemRepository : IItemRepository
    {
        private readonly ApplicationDbContext _dbContext;
        public ItemRepository(ApplicationDbContext dbContext) {
            _dbContext = dbContext;
        }
        public async Task<ItemHeader> AddItemHeaderAsync(ItemHeader itemHeader)
        {
            _dbContext.ItemHeaders.Add(itemHeader);
            await _dbContext.SaveChangesAsync();
            return itemHeader;
        }

        public async Task<ItemHeader> AddItemHeaderWithLinesAsync(ItemHeader itemHeader)
        {
            // Add the ItemHeader and line to the database
            _dbContext.ItemHeaders.Add(itemHeader);
            await _dbContext.SaveChangesAsync();
            return itemHeader;
        }

        public async Task<ItemLine> AddItemLineAsync(ItemLine itemLine)
        {
            _dbContext.ItemLines.Add(itemLine);
            await _dbContext.SaveChangesAsync();
            return itemLine;
        }
    }
}

using itemdetails.Models;
using itemdetails.Service;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace itemdetails.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ItemController : ControllerBase
    {
        private readonly IItemService _itemService;
        public ItemController(IItemService itemService) {
            _itemService = itemService;
        }

        [HttpPost("Create")]
        public async Task<IActionResult> CreateItemHeaderWithLines([FromBody] ItemHeaderDto itemHeaderDto)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            // Map DTO to domain models
            var itemHeader = new ItemHeader
            {
                HEADERNAME = itemHeaderDto.HeaderName,
                CREATEDDATE = itemHeaderDto.CreatedDate,
                ItemLines = itemHeaderDto.ItemLines.Select(line => new ItemLine
                {
                    LINEDESCRIPTION = line.LineDescription,
                    QUANTITY = line.Quantity
                }).ToList()
            };

            var createdHeader = await _itemService.CreateItemHeaderWithLinesAsync(itemHeader);
            return CreatedAtAction(nameof(CreateItemHeaderWithLines), new { id = createdHeader.HEADERID }, createdHeader);
        }

    }
}

using itemdetails.Models;
using Microsoft.EntityFrameworkCore;

namespace itemdetails.Context
{
    public class ApplicationDbContext: DbContext
    {
        public DbSet<ItemHeader> ItemHeaders { get; set; }
        public DbSet<ItemLine> ItemLines { get; set; }
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // ItemHeader configuration
            modelBuilder.Entity<ItemHeader>(entity =>
            {
                entity.ToTable("ITEMHEADER"); // Matches table name in database
                entity.HasKey(e => e.HEADERID);
            });

            // ItemLine configuration
            modelBuilder.Entity<ItemLine>(entity =>
            {
                entity.ToTable("ITEMLINE"); // Matches table name in database
                entity.HasKey(e => e.LINEID);

                // Foreign Key Relationship
                entity.HasOne(e => e.ItemHeader)
                      .WithMany(h => h.ItemLines)
                      .HasForeignKey(e => e.HEADERID)
                      .OnDelete(DeleteBehavior.Cascade);
            });
        }
    }
}

Program.cs:

using itemdetails.Context;
using Microsoft.Extensions.Options;
using System.Configuration;
using Oracle.ManagedDataAccess.Client;
using itemdetails.Service;
using itemdetails.Repository;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
builder.Services.AddDbContext<ApplicationDbContext>(options =>
        options.UseOracle(builder.Configuration.GetConnectionString("OracleConnection")));
//builder.Services.AddDbContext<ApplicationDbContext>(options=> options.oracle(Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IItemService, ItemService>();
builder.Services.AddScoped<IItemRepository, ItemRepository>();

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseAuthorization();

app.MapControllers();

app.Run();

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "OracleConnection": "User Id=c##ekycdev;Password=ekycdev;Data Source=DESKTOP-8OFULSU:1521/XE"
  }
}

Postman:

{

  "HeaderName": "Header 1",

  "CreatedDate": "2024-12-27T00:00:00",

  "ItemLines": [

    {

      "LineDescription": "Item Line 1",

      "Quantity": 10

    },

    {

      "LineDescription": "Item Line 2",

      "Quantity": 5

    }

  ]

}

Output:

{

    "headerid": 6,

    "headername": "Header 1",

    "createddate": "2024-12-27T00:00:00",

    "itemLines": [

        {

            "lineid": 13,

            "headerid": 6,

            "linedescription": "Item Line 1",

            "quantity": 10

        },

        {

            "lineid": 14,

            "headerid": 6,

            "linedescription": "Item Line 2",

            "quantity": 5

        }

    ]

}