EF one to many on part of composite primary key

c# ef-core-2.0 ef-fluent-api entity-framework-core

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.

1
1
1/12/2019 7:24:29 PM

Accepted Answer

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.

2
11/27/2018 8:33:26 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow