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