EF one to many on part of composite primary key

Question

I have three layers of tables in an existing database and I'm trying to include the bottom level records when I get the middle level data... This should be a one to many relationship - for shipment x with product y there are z analysis results.

public class Shipment { [Key] public int Id { get; set; } public string ShipName { get; set; } public DateTime ShipmentDate { get; set; } } public class ShipmentDetails { [ForeignKey ("ShipmentId")] public int Id { get; set; } [ForeignKey ("ProductId")] public int ProductId { get; set; } Public double Weight { get; set; } public virtual ShippingAnalysis Analysis { get; set; } } public class ShipmentAnalysis { [ForeignKey ("ShipmentId")] public int Id { get; set; } [ForeignKey ("ProductId")] public int TenantId { get; set; } [ForeignKey ("MetricId")] public int MetricId { get; set; } Public double Result { get; set; } }

I'm using the fluent api way of defining the composite primary keys.

modelBuilder.Entity<ShippingDetail>() .HasKey(c => new { c.ShipmentId, c.ProductlId }); modelBuilder.Entity<ShippingAnalysis>() .HasKey(c => new { c.ShipmentId, c.ProductId, c.MetricId });

I get the Shipping detail with the (one to many) analysis records.

var results = _context.ShippingDetail.Include(sd => sd.Analysis) .Where(sd => sd.ShipmentId == id);

This does not return a result in postman, but through the browser returns malformed JSON. If I drop the include, it works fine.

i would recommend not using a composite key, but instead use a dedicated PK AND adding unique constraints to avoid the duplicates — jazb
– jazb, Commented Nov 27, 2018 at 1:12
Thanks @Johnb, but I'm not sure how that would help with me getting the one to many analysis records with the shipping details results? — goneos
– goneos, Commented Nov 27, 2018 at 1:16

Ivan Stoev · Accepted Answer · 2018-11-27 08:33:26Z

The problem is not composite key, but navigation property (hence relationship definition). The navigation property at (one) side (when present) must be a collection and navigation property at (many) side should be reference - see Relationships - Definition of Terms.

According to

modelBuilder.Entity<ShippingDetail>() .HasKey(c => new { c.ShipmentId, c.ProductlId }); modelBuilder.Entity<ShippingAnalysis>() .HasKey(c => new { c.ShipmentId, c.ProductId, c.MetricId });

the relationship should be ShippingDetail (one) -> (many) ShippingAnalysis, hence

public virtual ShippingAnalysis Analysis { get; set; }

property of ShippingDetail must be

public virtual ICollection<ShippingAnalysis> Analysis { get; set; }

This should be enough for EF Core to determine the correct composite FK columns. But if you want to be hundred percent sure (being explicit never hurts), add the following fluent configuration:

modelBuilder.Entity<ShippingDetail>() .HasMany(e => e.Analysis) .WithOne() // make sure to specify navigation property if exists, e.g. e => e.NavProp .HasForeignKey(e => new { e.ShipmentId, e.ProductId });

P.S. Remove all these [ForeignKey] data annotations. They do different things depending on whether they are applied on FK property or navigation property, and for sure don't do what you think, and sometimes may actually lead to unexpected behaviors. Based on my experience with EF Core relationships, either let EF Core conventions do their job, or use fluent API.

I've put this in place as per your above code AND removed the FK stuff on the ShippingDetail entity. I am no getting an error that says 'The entity type 'ShippinDetail' requires a primary key to be defined.' I DID have a fluent API composite foreign as per my original post, but had commented it out when I put in your code. So... I uncommented it and then there is NO error, but it times out and returns '['. Where am I going wrong?

Collectives™ on Stack Overflow

EF one to many on part of composite primary key

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related