Insert a record to many table

Question

I am building an API using .NET Core and I am trying to insert records into many tables in relationships. I have tables Client, Shipment, ShipmentDetail.

How can I insert records into Client table then last client id must insert into Shipment table then shipmentId (last added one) must insert into shipmentdetails. And should I do all of these in one controller?

Classes:

public class Client
{
        public int ClientId { get; set; }
        public string Name { get; set; } = String.Empty;
        public string Adress { get; set; }
        public List<Shipment> Shipments { get; set; }
}

public class Shipment
{
        public int ShipmentId { get; set; }
        public DateTime TimeCreated { get; set; }
        public string ShippingAddress { get; set; }
        public int DeliveryCost { get; set; }
        public string OrderNumber { get; set; }
        public DateTime EstimatedDate { get; set; }
        public int ClientId { get; set; }

        public Client Client { get; set; }

        public List<ShipmentDetail>? ShipmentDetails { get; set; }
        public List<ShipmentStatu>? ShipmentStatus { get; set; }
}

public class ShipmentDetail
{
        public int Id { get; set; }
        public string DeliveryNumber { get; set; }
        public string TrackingNumber { get; set; }

        public Shipment Shipment { get; set; }
        public int ShipmentId { get; set; }

        public Product Product { get; set; }
        public int ProductId { get; set; }
}

What I have tried: I am checking client name and if client exists, I am getting id, else I am inserting a new client and getting new ClientId:

[HttpPost]
public async Task<ActionResult<List<Shipment>>> AddShipments(ShipmentDto request)
{
        var client = _context.Clients.Where(p => p.Name == request.ClientName).Select(p => p.ClientId)

        if (client.ToList().Count == 0)
        {
            var newClient = new Client
            {
                Name = request.ClientName,
                Adress = request.ClientAdress
            };

            _context.Clients.Add(newClient);
            _context.SaveChanges();

            var newShipment = new Shipment
            {
                ClientId = newClient.ClientId,
                TimeCreated = Convert.ToDateTime(DateTime.Now.GetDateTimeFormats()[0]),
                ShippingAddress = request.ShippingAddress,
                DeliveryCost = request.DeliveryCost,
                OrderNumber = "x" + DateTime.Now.ToString("MMddyy"),
                EstimatedDate = Convert.ToDateTime(DateTime.Now.AddDays(2).GetDateTimeFormats()[0]),
            };

            _context.Shipments.Add(newShipment);
            _context.SaveChanges();
        }
        else
        {
            var newShipment = new Shipment
            {
                ClientId = client.FirstOrDefault(),
                TimeCreated = Convert.ToDateTime(DateTime.Now.GetDateTimeFormats()[0]),
                ShippingAddress = request.ShippingAddress,
                DeliveryCost = request.DeliveryCost,
                OrderNumber = "x" + DateTime.Now.ToString("MMddyy"),
                EstimatedDate = Convert.ToDateTime(DateTime.Now.AddDays(2).GetDateTimeFormats()[0]),

            };

            _context.Shipments.Add(newShipment);
            _context.SaveChanges();
        }

    var shipment = _context.Shipments.Max(p => p.ShipmentId);

    return Ok(shipment);
}

It works but I don't know what is the best practice for it and should I use many dto or endpoint for nested inserts?

Answer 1

Best practices regarding the question you asked and that it would be good to apply in your code in general;

• Send as few requests to the database as possible,
• Try to use asynchronous methods,
• Research "Repository", "Generic Repository" and "Unit of Work" design patterns,
• If you set the property Id to be the primary key of the table, [Key] and if you mark it with [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attributes, the database will automatically generate a unique primary key for this column. In this way, choosing the error catching option instead of writing the first condition in your controller method will prevent you from doing extra checking.
• Mark the navigation properties as virtual,
• Do not include business logic in the controller as much as possible. Instead, write services and use these services with dependency injection.
• Finally, at the end of your method, you are getting the relevant shipment from the database and return it as a response. You don't need to do this. Entity Framework tracks the objects you are processing, so that the object you are processing will be like its final version in the database.

I will make the code sample based only on your question, apart from what I wrote above. You can apply changes to the above yourself.

public class Client
{
    public int ClientId { get; set; }
    public string Name { get; set; } = String.Empty;
    public string Adress { get; set; }
    public virtual List<Shipment> Shipments { get; set; }
}

public class Shipment
{
    public int ShipmentId { get; set; }
    public DateTime TimeCreated { get; set; }
    public string ShippingAddress { get; set; }
    public int DeliveryCost { get; set; }
    public string OrderNumber { get; set; }
    public DateTime EstimatedDate { get; set; }
    public int ClientId { get; set; }

    public virtual Client Client { get; set; }

    public virtual List<ShipmentDetail>? ShipmentDetails { get; set; }
    public virtual List<ShipmentStatu>? ShipmentStatus { get; set; }
}

public class ShipmentDetail
{
    public int Id { get; set; }
    public string DeliveryNumber { get; set; }
    public string TrackingNumber { get; set; }

    public virtual Shipment Shipment { get; set; }
    public int ShipmentId { get; set; }

    public virtual Product Product { get; set; }
    public int ProductId { get; set; }
}

---

[HttpPost]
public async Task<ActionResult<List<Shipment>>> AddShipments(ShipmentDto request)
{
    var clientId = _context.Clients.FirstOrDefault(p => p.Name == request.ClientName)?.ClientId;

    Shipment newShipment = null;

    if (clientId == null)
    {
        newShipment = new Shipment
        {
            //ClientId = newClient.ClientId ===>  No need anymore. Entity Framework will assign the relavent client id.
            TimeCreated = Convert.ToDateTime(DateTime.Now.GetDateTimeFormats()[0]),
            ShippingAddress = request.ShippingAddress,
            DeliveryCost = request.DeliveryCost,
            OrderNumber = "x" + DateTime.Now.ToString("MMddyy"),
            EstimatedDate = Convert.ToDateTime(DateTime.Now.AddDays(2).GetDateTimeFormats()[0]),
        };

        var newClient = new Client
        {
            Name = request.ClientName,
            Adress = request.ClientAdress,
            Shipments = new List<Shipment>
            {
                newShipment
            }
        };

        _context.Clients.Add(newClient);
        _context.SaveChanges();
    }
    else
    {
        newShipment = new Shipment
        {
            ClientId = clientId,
            TimeCreated = Convert.ToDateTime(DateTime.Now.GetDateTimeFormats()[0]),
            ShippingAddress = request.ShippingAddress,
            DeliveryCost = request.DeliveryCost,
            OrderNumber = "x" + DateTime.Now.ToString("MMddyy"),
            EstimatedDate = Convert.ToDateTime(DateTime.Now.AddDays(2).GetDateTimeFormats()[0]),

        };

        _context.Shipments.Add(newShipment);
        _context.SaveChanges();
    }

    return Ok(newShipment);
}

That's should work.

---

A simplified version of it could be

[HttpPost]
public async Task<ActionResult<List<Shipment>>> AddShipments(ShipmentDto request)
{
    // Get the first client or create a new one (it is better if you have unique identifier for a client
    var client = _context.Clients.FirstOrDefault(p => p.Name == request.ClientName) 
     ?? new Client
        {
            Name = request.ClientName,
            Adress = request.ClientAdress
        };

    var newShipment = new Shipment
    {
        TimeCreated = Convert.ToDateTime(DateTime.Now.GetDateTimeFormats()[0]),
        ShippingAddress = request.ShippingAddress,
        DeliveryCost = request.DeliveryCost,
        OrderNumber = "x" + DateTime.Now.ToString("MMddyy"),
        EstimatedDate = Convert.ToDateTime(DateTime.Now.AddDays(2).GetDateTimeFormats()[0]),
        ShipmentDetails = new List<ShipmentDetail>{
           new ShipmentDetail{
              DeliveryNumber = "1",
              //Other properties
           }
        }
    };

   if(client.Shipments == null)
      client.Shipments = new List<Shipment>();

   client.Shipments.Add(newShipment);

   if(client.ClientId == default)
      _context.Clients.Add(client);
   else _context.Clients.Update(client)

   _context.SaveChanges();

   return Ok(newShipment);
}