Managing One To Many Relationships With Entity Framework Core-CodePudding

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 and Product

I am getting error when i am trying to insert shipmentDetails into shipment. The error is: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ShipmentDetails_Products_ProductId". The conflict occurred in database "MyDb", table "dbo.Products", column 'Id'.

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 Product : IEntity
    {
        public int Id { get; set; }
        public string Name { 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

[HttpPost]
public async Task<ActionResult<List<Shipment>>> AddShipments(ShipmentDto request)
{
 
    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 = request.DeliveryNumber ,
                  TrackingNumber=request.TrackingNumber,    
           }
        }
    };

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

Does anyone have an idea ? Kind Regards...

CodePudding user response：

A couple issues and suggestions when working with related collections.

First, when loading entities with the intention of working with their children, be sure to eager load their children:

var client = _context.Clients
    .Include(p => p.Shipments)
    .SingleOrDefault(p => p.Name == request.ClientName) 
    ?? new Client
    {
        Name = request.ClientName,
        Adress = request.ClientAdress
    };

Where you expect 1 or 0 results, use SingleOrDefault() rather than FirstOrDefault(). Using the "First" flavour methods can conceal bad data assumptions where your code is not selective enough resulting in the potential for the wrong data to be updated. The proper use of "Single" methods assert this at runtime and would throw the moment they encounter an invalid data state for the assumed uniqueness.

If you know there could be more than one match and only want the first particular match, then you can use FirstOrDefault() but should include an OrderBy() to ensure the selection is predictable and repeatable. I know Nick Chapas recently released a video about where SingleOrDefault() could impose performance costs compared to FirstOrDefault(), but that only applies to using it against in-memory collections. When EF builds a query, FirstOrDefault generates a TOP(1) while SingleOrDefault generates a TOP(2) equivalent query.

Next, you should avoid ever "setting" collection references in entities outside the construction of the entity. EF works with proxies for change tracking so setting a collection to a new List<T> can muck things up with existing entities. I recommend changing the collection references in Client to something like:

public virtual ICollection<ShipmentDetail> ShipmentDetails { get; internal set; } = new List<ShipmentDetail>();
public virtual ICollection<ShipmentStatus> ShipmentStatus { get; internal set; } = new List<ShipmentStatus>();

Now you can use these collections immediately on a new Client instance, or access them without worrying about #null checks. There should never be code anywhere in the code base that attempts to set these to a new collection.

The resulting code would end up looking like:

var client = _context.Clients
    .Include(p => p.Shipments)
    .SingleOrDefault(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]),
    }
};
newShipment.ShipmentDetails.Add(new ShipmentDetail
{
    DeliveryNumber = request.DeliveryNumber,
    TrackingNumber = request.TrackingNumber    
});

client.Shipments.Add(newShipment);

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

_context.SaveChanges();

return Ok(newShipment);

The other notable here was that we don't need to call _context.Update, instead, let EF's change tracking manage tracking what details in the client and related entities has occurred. When using DTOs / ViewModels I'd recommend also being consistent and avoiding sending Entities in their place, so I would consider updating the returned object in the response to a DTO.