CodeFirst Foreign Key to two properties

Question

I'd like to use one property as a foreign key to two seperate entities - once as part of a composite foreign key, and once as a single foreign key. Here's a quick example of what I mean:

public class ParentEntity{
    [Key, Column(Order = 1)]
    String Id {get; set;}
    [Key, Column(Order = 2), ForeignKey("Year")]
    int YearId {get; set;}

    [ForeignKey("YearId")]
    Year Year;
    virtual ICollection<ChildEntity> Children {get; set;}
}

public class ChildEntity{
    [Key, Column(Order = 0), ForeignKey("Parent")]
    String Id {get; set;}
    [Key, Column(Order = 1), ForeignKey("Parent")]
    String ParentId {get; set;}
    [Key, Column(Order = 2), ForeignKey("Parent, Year")]
    int YearId {get; set;}

    [ForeignKey("ParentId, YearId")]
    ParentEntity Parent{get; set;}

    [ForeignKey("YearId")]
    Year Year {get; set;}
}

public class Year{
    [Key]
    int YearId {get; set;}
}

The YearId/Year Property in ChildEntity should serve as both part of the primary key (ParentId / Year) to ParentEntity, and as a foreign key to the Year table.

However, the tag ForeignKey("Parent, Year") is not valid, because it can only take one parameter to a navigation property. This is the error message I get:

Additional information: The ForeignKeyAttribute on property 'YearId' on type 'CodeFirst.Models.ChildEntity' is not valid. 
The navigation property 'Parent, Year' was not found on the dependent type 'CodeFirst.Models.ChildEntity'. 
The Name value should be a valid navigation property name.

Which I suppose makes sense, it's looking for a navigation property named 'Parent, Year' instead of two navigation properties, named 'Parent' and 'Year'.

How can I force EntityFramework/CodeFirst to recognise this pattern correctly?

NB: I named the entities 'parent' / 'child', but there is no inheritance relation between them (it is in fact an ownership relation).

Just don't use composite keys. It's an anti-pattern that makes your code unnecessarily complicated, without purpose. The whole point of a primary key is that it's unique. There's no need to combine it with anything else. — Chris Pratt

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2017-09-11T14:28:07

You only need the ForeignKeyAttribute on the navigation property or the foreign key property. Not both. So like this:

    public class ParentEntity
    {
        [Key, Column(Order = 1)]
        public String Id { get; set; }
        [Key, Column(Order = 2)]
        public int YearId { get; set; }

        [ForeignKey("YearId")]
        Year Year;
        public virtual ICollection<ChildEntity> Children { get; set; }
    }

    public class ChildEntity
    {
        [Key, Column(Order = 0)]
        public String Id { get; set; }
        [Key, Column(Order = 1)]
        public String ParentId { get; set; }
        [Key, Column(Order = 2)]
        public int YearId { get; set; }

        [ForeignKey("ParentId, YearId")]
        public ParentEntity Parent { get; set; }

        [ForeignKey("YearId")]
        public Year Year { get; set; }
    }

    public class Year
    {
        [Key]
        public int YearId { get; set; }
    }

CodeFirst Foreign Key to two properties

2 Answers