Error in relationship between two models have more keys in asp.net core 2.1

asp.net asp.net-mvc c# entity-framework-core entity-relationship

Question

Problem:

The relationship from 'SalesFooter.SaleHeaders' to 'SalesHeader.SalesFooters' with foreign key properties {'SalesOrderNo' : int} cannot target the primary key {'SalesOrderNo' : int, 'SalesType' : int, 'SalesYear' : int, 'BranchCode' : int} because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.

Details:

I am working in ASP.NET Core, Entity Framework Core 2.1 with Visual Studio 2017. When I make relationship between two models SalesHeader and SalesFooter as follows get the above error:

public class SalesHeader  
{        

        public int SalesOrderNo { get; set; }  


        public int SalesYear { get; set; }  


        public int BranchCode { get; set; }  


        public int SalesType { get; set; }  
        [Required]  

        public DateTime SalesDate { get; set; }  
        public ICollection<SalesFooter> SalesFooters { get; set; }  

}

public class SalesFooter  
{  

        public int SalesOrderNo { get; set; }  

        public int SalesYear { get; set; }  

        public int BranchCode { get; set; }  

        public int SalesType { get; set; }  

        public int SalesLineNo { get; set; }  
        [Required]  

        public DateTime SalesDate { get; set; }  
        [DataType("decimal(18 ,2")]  
        public decimal Quantity { get; set; }  
        [DataType("decimal(18 ,2")]  
        public decimal UnitPrice { get; set; }  
        [DataType("decimal(18 ,2")]  
        public decimal Total { get; set; }  


        public SalesHeader SaleHeaders{ get; set; }  
}

protected override void OnModelCreating(ModelBuilder modelBuilder)  
{  
     base.OnModelCreating(modelBuilder);  

     modelBuilder.Entity<SalesHeader>()  
                .HasKey(t => new { t.SalesOrderNo,t.SalesType,t.SalesYear,t.BranchCode });  
     modelBuilder.Entity<SalesFooter>()  
                .HasKey(t => new { t.SalesOrderNo, t.SalesType, t.SalesYear, t.BranchCode,t.SalesLineNo});  
     modelBuilder.Entity<SalesFooter>().HasOne(e => e.SaleHeaders)  
                     .WithMany(e => e.SalesFooters)  
                     .HasForeignKey(e => e.SalesOrderNo);      
 }  

How to make relation between two models based on keys above ?

Sample Data:

SalesHeader Table

........................................

SalesOrderNo  SalesYear  BranchCode SalesType  CustomerID  
50              2018        1          1          20  

SalesFooter Table

.............................

SalesOrderNo SalesLineNo  SalesYear  BranchCode SalesType  ItemCode  
50                1          2018        1          1          1001  
50                2          2018        1          1          1002  
50                3          2018        1          1          1003 
1
1
11/19/2018 2:00:15 AM

Popular Answer

Here

.HasForeignKey(e => e.SalesOrderNo)

you are telling EF Core to use a single property(column) SalesFooter.SalesOrderNo as a FK to SalesHeader. But SalesHeader uses composite PK {SalesOrderNo, SalesType, SalesYear, BranchCode}.

The type of the FK and the reference PK must match. Which means you need to setup a composite FK {SalesOrderNo, SalesType, SalesYear, BranchCode}.

You can do that by simply removing the whole

modelBuilder.Entity<SalesFooter>()
    .HasOne(e => e.SaleHeaders)
    .WithMany(e => e.SalesFooters)  
    .HasForeignKey(e => e.SalesOrderNo);

or just the

    .HasForeignKey(e => e.SalesOrderNo)

fluent configuration because your model/relationship follow the EF Core conventions, hence EF Core can automatically identify the relationship with the correct navigation/FK/PK properties involved.

But if you want to be explicit, then use the same syntax as for defining composite PK:

modelBuilder.Entity<SalesFooter>()
    .HasOne(e => e.SaleHeaders)
    .WithMany(e => e.SalesFooters)  
    .HasForeignKey(e => new { e.SalesOrderNo, e.SalesType, e.SalesYear, e.BranchCode }); // <--
2
11/19/2018 8:05:35 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