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?

Actually, You don't need to use `_context.SaveChanges();` every time you add data, You only need to use it once after the last time the data was added. — Xinran Shen, Aug 29 '22 at 08:18

bugrakosen · Accepted Answer · 2022-08-30T17:26:35.180

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);
}

Thank you so much for your comment buğra, Now I tried what you said but for your first solution I got this error:System.Text.Json.JsonException: A possible object cycle was detected. And for your second solution i am getting: : 'Object reference not set to an instance of an object.' --> client.Shipments.Add(newShipment);(for this add line) — Commodore, Aug 29 '22 at 07:01
You can fix the first error with reference loop handling in serializer. I don't know if System.Text.Json has this feature but you can also use Newtonsoft. ``services.AddControllers().AddNewtonsoftJson(options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore );`` code will solve the error. For the second error you need to tell me which piece of code the error is in. @Commodore — bugrakosen, Aug 29 '22 at 07:50
i got second error for this line, client.Shipments.Add(newShipment); — Commodore, Aug 29 '22 at 08:06
clientId and ShipmentId returning as a 0 : shipmentId": 0, "timeCreated": "2022-08-29T00:00:00", "shippingAddress": "example", "deliveryCost": 0, "orderNumber": "x082922", "estimatedDate": "2022-08-31T00:00:00", "clientId": 0, — Commodore, Aug 29 '22 at 17:57
In the first line of the method, ``client.Shipments`` may return null if client data is found. To prevent this, search the internet for Lazy Loading or Eager Loading. — bugrakosen, Aug 29 '22 at 20:43
Thank you for all your answer, it was educational for me as a beginner in the .net — Commodore, Aug 30 '22 at 06:13
I am trying to insert, ShipmentDetails into shipments but i am getting error: INSERT statement conflicted with the FOREIGN KEY constraint "FK_ShipmentDetails_Products_ProductId". If you help me again that would awesome — Commodore, Aug 31 '22 at 16:13
Please see [here](https://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint-sql-server) — bugrakosen, Sep 01 '22 at 08:21

Insert a record to many table

1 Answers1