Unable to determine a composite foreign key ordering for foreign key

Question

Person is the user model that includes all users. The change model includes the EngineerId and the ManagerId, both are Person IDs. Why am I getting this error?

Unable to determine a composite foreign key ordering for foreign key on type ProjectName.Models.Change. When using the ForeignKey data annotation on composite foreign key properties ensure order is specified by using the Column data annotation or the fluent API.

public class Change
{
    [Key]
    public int ChangeId { get; set; }

    [Required(ErrorMessage = "Change description is required.")]
    [Display(Name = "Change Description")]
    [DataType(DataType.MultilineText)]
    public string ChangeDescription { get; set; }

    [Required(ErrorMessage = "Change date is required.")]
    [Display(Name = "Date of Change")]
    [DataType(DataType.Date)]
    public DateTime ChangeDate { get; set; }

    [Required(ErrorMessage = "Time is required.")]
    [Display(Name = "Time of Change")]
    [DataType(DataType.Time)]
    public DateTime ChangeTime { get; set; }

    [Required(ErrorMessage = "Engineer name is required.")]
    [Display(Name = "Engineer")]
    [ForeignKey("person")]
    public int EngineerId { get; set; }

    [Required(ErrorMessage = "Status is required.")]
    [Display(Name = "Status")]
    public int StatusId { get; set; }

    [Required(ErrorMessage = "Manager is required.")]
    [Display(Name = "Manager")]
    [ForeignKey("person")]
    public int ManagerId { get; set; }

    [Required(ErrorMessage = "System is required.")]
    [Display(Name = "System")]        
    public int SystemDetailId { get; set; }

    public virtual Person person { get; set; }
    public virtual Status status { get; set; }
    public virtual SystemDetail systemdetail { get; set; }
}

@CodeCaster I think the issue here is different - the OP does **not** want composite FK. Would you mind to reopen it? — Ivan Stoev, Oct 17 '16 at 15:37
@CodeCaster yes this is what I was referring to `[Key, Column(Order = index)]`--Thanks — inan, Oct 17 '16 at 15:41
@Nathan McKaskle Please confirm that you don't want composite FK, so I can reopen the question. — Ivan Stoev, Oct 17 '16 at 15:43
You can't call this a duplicate, we're talking about MVC 6 here, this is YEARS later. That other question did not answer my question and there was no such thing as annotations back then so far as I am aware. — Nathan McKaskle, Oct 17 '16 at 16:06
@IvanStoev As someone who's relatively new to ASP.NET MVC, I don't know what you're asking or what you mean. The question cited as duplicate does not answer my question and is nearly 6 years old. I need to understand how to solve this in modern times. — Nathan McKaskle, Oct 17 '16 at 16:10
The question cited as duplicate is also dealing with SQL 2008, not a local db created by Visual Studio 2015 in code first. — Nathan McKaskle, Oct 17 '16 at 16:12
@NathanMcKaskle I was trying to help you by reopening the question because my opinion was that it's **not** a duplicate. Just needed your confirmation. So we are on one and same page, mate:) — Ivan Stoev, Oct 17 '16 at 16:14
If I understand correctly, you have [two foreign keys to the same table](http://stackoverflow.com/questions/28570916/defining-multiple-foreign-key-for-the-same-table-in-entity-framework-code-first) and you will need a second `Person` property. — Jasen, Oct 17 '16 at 18:02
@Nathan you're confusing everything. This has _nothing_ to do with MVC, it's purely Entity Framework. The constructs of that library hardly changed over time. Also, if you find a related question that you think does not apply anymore, **mention it**. I'll happily agree that I might have misunderstood your question and chosen the wrong duplicate, but that doesn't change that your question should explain very explicitly what you're trying to do, what you changed since it last worked and what your research towards this error turned up. — CodeCaster, Oct 17 '16 at 18:14
See also [Entity Framework Code First - two Foreign Keys from same table](http://stackoverflow.com/questions/5559043/entity-framework-code-first-two-foreign-keys-from-same-table), the first Google hit for "ef code first two foreign keys to same table". — CodeCaster, Oct 17 '16 at 18:20

Ivan Stoev · Accepted Answer · 2016-10-17T19:31:33.650

You have 2 foreign key properties (EngineerId and ManagerId) that you have associated (via ForeignKey attribute) with the one and the same navigation property (person). This way EF thinks they form a composite key, hence the exception you are getting.

Since (if I understand correctly) your intent is to have 2 relationships, you need to associate each foreign key with a separate navigation property. Here is the essential part that you need to change:

public class Change
{
    // ...

    [Required(ErrorMessage = "Engineer name is required.")]
    [Display(Name = "Engineer")]
    [ForeignKey("Engineer")]
    public int EngineerId { get; set; }

    [Required(ErrorMessage = "Manager is required.")]
    [Display(Name = "Manager")]
    [ForeignKey("Manager")]
    public int ManagerId { get; set; }

    // instead of person property:
    public virtual Person Engineer { get; set; }
    public virtual Person Manager { get; set; }
}

Thanks, this is the modern answer I was looking for. – Nathan McKaskle Oct 17 '16 at 20:03 — Nathan McKaskle, Oct 17 '16 at 20:03

Unable to determine a composite foreign key ordering for foreign key

1 Answers1

Linked