The Foreign Key on table with columns could not be created because the principal key columns could not be determined

The Foreign Key on table with columns could not be created because the principal key columns could not be determined. Use the AddForeignKey fluent API to fully specify the Foreign Key.

This error takes place when the code first model is trying to create a foreign key to a table that is not included in the model. Since Entity Framework (EF) can’t find the model that contains the primary key association, it raises the error. 

To enable a foreign key to a non-existent model in you project, just use the fluent API to add or remove the constraint. This enables us to basically run SQL queries on the target database. To illustrate this problem, let’s take a look at the following model:

    [Table("App")]

    public partial class App

    {      

        [Key]

        public int Id { get; set; }      

        [Required]

        [StringLength(150)]

        public string Name { get; set; }

        /// <summary>

        /// add this field as a FK to the dbo.Roles table

        /// which does not exist in my model

        /// </summary>       

        public string RoleId { get; set; }       

    }

We want to create a foreign key on the RoleId field, but we do not have the dbo.Roles table model defined in the project. The migration code attempts to do that by just calling the fluent API  ForeignKey  as shown below:

CreateTable(

                "dbo.App",

                c => new

                    {

                        Id = c.Int(nullable: false, identity: true),

                        Name = c.String(nullable: false, maxLength: 150),

                        RoleId = c.String(),

                    })

                .PrimaryKey(t => t.Id)

                .ForeignKey("dbo.MyRoles", t => t.RoleId).Index(t => t.RoleId);

The highlighted line generates the error because the mapping between RoleId to the primary key on the dbo.Roles table is not defined or is unknown by EF.  To make it explicit, we can change the code as the error suggests to using the AddForeignKey API and removing the fluent method ForeignKey as follows:

CreateTable(

                "dbo. App",

                c => new

                    {

                        Id = c.Int(nullable: false, identity: true),

                        Name = c.String(nullable: false, maxLength: 150),

                        RoleId = c.String(),

                    })

                .PrimaryKey(t => t.Id).Index(t => t.RoleId);;

AddForeignKey("dbo.App", "RoleId", "dbo.Roles","Id");

We should notice how on this method we are explicitly indicating that we want to map the RoleId column to the Primay key column of Id on the Roles table.

We can now build the code and run the database update preview to see what SQL script would be executed by using this command:

Update-Database  -script

Note: Run this on the Package Manager Console

Thanks for reading.