Code First migration when entities have cross references with foreign keys

Question

I have models that have references to each other:

public class Dept
{
    [Key]
    public int DeptId { get; set; }

    [ForeignKey("ManagerId")]
    public Emp Manager { get; set; }

    public int? ManagerId { get; set; }

    public string DeptName { get; set; }
}

public class Emp
{
    [Key]
    public int EmpId { get; set; }

    [Required]
    [ForeignKey("DeptId")]
    public Dept Dept { get; set; }

    public int DeptId { get; set; }

    public string Name { get; set; }
}

When I call Add-Migration, I get error:

The ForeignKeyAttribute on property 'Manager' on type 'App.Dept' is not valid. The foreign key name 'ManagerId' was not found on the dependent type 'App.Emp'. The Name value should be a comma separated list of foreign key property names.

What should I do to create migration with these tables?

UPD: Implicit optional Manager does not solve the problem:

modelBuilder.Entity<Emp>().HasRequired(_ => _.Dept).WithOptional(_ => _.Manager);

UPD2: Dept:Emp relation is 1:0..1

UPD3: Maybe another relation will be added to Dept model but it will be also 1:0..1:

[ForeignKey("ManagerId")]
public Emp CTO { get; set; }
public int? CTOId { get; set; }

It is not one to many relation: one department have zero or one manager, and zero or one CTO. At the moment I have only one relation, but I want to name the field ManagerId, not a EmpId.

UPD4: Schema from the beginning of my question with two primary/foreign keys relation (Dept.DeptId/Emp.DeptId, Emp.EmpId/Dept.ManagerId) works in plain SQL. I know workarounds with additional table or without foreign keys, but I need an answer how to make work schema above or why it is not working in EF.

Answer 1

From your class code you have the following

For 1-1 and 1-0..1, same primary key should appear in both tables and in your design its not the case since both tables will have their own primary key

Now, based on the code you put, the configuration should be as follows

 modelBuilder.Entity<Dept>()
             .HasKey(t => t.DeptId)
             .HasOptional(t => t.Manager)
             .WithRequired(t => t.Dept);

but this will not mean a 1-1 or 1-0..1 relation.

if you want to convert your codes to become 1-0..1, then you code should be like this

1. Remove the EmpId from the class Emp

2. The configuration should look like

   modelBuilder.Entity<Emp>()
               .HasKey(t => t.DeptId)
               .HasRequired(t => t.Dept);
   modelBuilder.Entity<Dept>()
               .HasKey(t => t.DeptId)
               .HasOptional(t => t.Manager)
               .WithRequired(t => t.Dept);
   modelBuilder.Entity<Dept>()
               .HasOptional(t => t.Manager)
               .WithMany()
               .HasForeignKey(t => t.ManagerId)
               .WillCascadeOnDelete(false);
   

for more information about the relations, you might read this article Configure One-to-Zero-or-One Relationship:

Hope this will help you

Answer 2

You have mainly three way to configure 1-1 relationships (the case of your error is the 3rd explained case).

Complex types
The first way is with only one table and using complex types. Choosing this configuration has performance impact (often, overall performances are better than other configurations but it depends on the record size and on how many times you have both records).

In your case you need only to mark one of the entities with ComplexType attribute

public class Dept
{
    [Key]
    public int DeptId { get; set; }

    public Emp Manager { get; set; }

    public string DeptName { get; set; }
}

[ComplexType]
public class Emp
{
    public int EmpId { get; set; } // You can still have this property but it will not be a primary key

    public string Name { get; set; }
}

With this model this is the created table

CREATE TABLE [Depts] (
 [DeptId] int not null identity(1,1)
, [Manager_EmpId] int not null
, [Manager_Name] text null
, [DeptName] text null
);

Standard foreign key
The second way is to use a standard foreign key. The model can have navigation properties on both classes, has 2 tables with independent primary keys but only 1 table has the foreign key to the other table (you wrote about this configuration on your question). You obtain this configuration overriding OnModelCreating. Using this way there are several configurations you can do using fluent API. The main option is where should EF insert the foreign key. In every configuration you must have Map method (I explain what happens without the Map method in the third way)

The model is always this

public class Dept
{
    [Key]
    public int DeptId { get; set; }

    public Emp Manager { get; set; }

    public string DeptName { get; set; }
}

public class Emp
{
    [Key]
    public int EmpId { get; set; }

    public Dept Department { get; set; }

    public string Name { get; set; }
}

WithRequiredPrincipal (1-1)

From MSDN: Configures the relationship to be required:required without a navigation property on the other side of the relationship. The entity type being > configured will be the principal in the relationship. The entity type that the relationship targets will be the dependent and contain a foreign > key to the principal.

modelBuilder.Entity<Dept>()
    .HasRequired(_ => _.Manager)
    .WithRequiredPrincipal(_ => _.Department)
    .Map(_ => _.MapKey("DepartmentId"));

This is the DDL generated

ExecuteNonQuery==========
CREATE TABLE [Depts] (
[DeptId] int not null identity(1,1)
, [DeptName] text null
);
ALTER TABLE [Depts] ADD CONSTRAINT [PK_Depts_c0491d33] PRIMARY KEY ([DeptId])
ExecuteNonQuery==========
CREATE TABLE [Emps] (
[EmpId] int not null identity(1,1)
, [Name] text null
, [DepartmentId] int not null
);
ALTER TABLE [Emps] ADD CONSTRAINT [PK_Emps_c0491d33] PRIMARY KEY ([EmpId])
ExecuteNonQuery==========
CREATE INDEX [IX_DepartmentId] ON [Emps] ([DepartmentId])
ExecuteNonQuery==========
ALTER TABLE [Emps] ADD CONSTRAINT [FK_Emps_Depts_DepartmentId] FOREIGN KEY ([DepartmentId]) REFERENCES [Depts] ([DeptId])

WithRequiredDependent (1-1)

From MSDN: Configures the relationship to be required:required without a navigation property on the other side of the relationship. [For me is not clear this explanation, anyway, for the real behaviour see below]

modelBuilder.Entity<Dept>()
    .HasRequired(_ => _.Manager)
    .WithRequiredDependent(_ => _.Department)
    .Map(_ => _.MapKey("EmpId"));

This is the DDL generated

ExecuteNonQuery==========
CREATE TABLE [Depts] (
[DeptId] int not null identity(1,1)
, [DeptName] text null
, [EmpId] int not null
);
ALTER TABLE [Depts] ADD CONSTRAINT [PK_Depts_bebceea2] PRIMARY KEY ([DeptId])
ExecuteNonQuery==========
CREATE TABLE [Emps] (
[EmpId] int not null identity(1,1)
, [Name] text null
);
ALTER TABLE [Emps] ADD CONSTRAINT [PK_Emps_bebceea2] PRIMARY KEY ([EmpId])
ExecuteNonQuery==========
CREATE INDEX [IX_EmpId] ON [Depts] ([EmpId])
ExecuteNonQuery==========
ALTER TABLE [Depts] ADD CONSTRAINT [FK_Depts_Emps_EmpId] FOREIGN KEY ([EmpId]) REFERENCES [Emps] ([EmpId])

WithOptional (1-0..1)

modelBuilder.Entity<Dept>()
    .HasRequired(_ => _.Manager)
    .WithOptional(_ => _.Department)
    .Map(_ => _.MapKey("ManagerId"));

This is the DDL generated

ExecuteNonQuery==========
CREATE TABLE [Depts] (
[DeptId] int not null identity(1,1)
, [DeptName] text null
, [ManagerId] int not null
);
ALTER TABLE [Depts] ADD CONSTRAINT [PK_Depts_ee5245bb] PRIMARY KEY ([DeptId])
ExecuteNonQuery==========
CREATE TABLE [Emps] (
[EmpId] int not null identity(1,1)
, [Name] text null
);
ALTER TABLE [Emps] ADD CONSTRAINT [PK_Emps_ee5245bb] PRIMARY KEY ([EmpId])
ExecuteNonQuery==========
CREATE INDEX [IX_ManagerId] ON [Depts] ([ManagerId])
ExecuteNonQuery==========
ALTER TABLE [Depts] ADD CONSTRAINT [FK_Depts_Emps_ManagerId] FOREIGN KEY ([ManagerId]) REFERENCES [Emps] ([EmpId])

You can use other methods to obtain similar configurations. I dont show here every example but we can mix these configurations

HasOptional/WithRequired
HasOptional/WithOptionalDependent
HasOptional/WithOptionalPrincipal

EF Default 0..1-1 1-0..1 1-1 configuration
This is how EF is interpreting your configuration. In this case EF generates 2 tables with dependent primary keys. On one table there is an independent primary key (in your case identity(1,1)) and on the other table you have a primary key that is also the foreign key. This is the default configuration. This is the only way to have foreign keys on both tables (not 2 constraints, no way to have 2 circular contraints, see below)

modelBuilder.Entity<Dept>()
    .HasRequired(_ => _.Manager)
    .WithRequiredPrincipal(_ => _.Department);

This is the DDL generated

ExecuteNonQuery==========
CREATE TABLE [Depts] (
[DeptId] int not null identity(1,1)
, [DeptName] text null
);
ALTER TABLE [Depts] ADD CONSTRAINT [PK_Depts_b91ed7c4] PRIMARY KEY ([DeptId])
ExecuteNonQuery==========
CREATE TABLE [Emps] (
[EmpId] int not null
, [Name] text null
);
ALTER TABLE [Emps] ADD CONSTRAINT [PK_Emps_b91ed7c4] PRIMARY KEY ([EmpId])
ExecuteNonQuery==========
CREATE INDEX [IX_EmpId] ON [Emps] ([EmpId])
ExecuteNonQuery==========
ALTER TABLE [Emps] ADD CONSTRAINT [FK_Emps_Depts_EmpId] FOREIGN KEY ([EmpId]) REFERENCES [Depts] ([DeptId])

This should be 1-1 relationship but if we look better there is one missing constraint. The primary key of the Dept table should be a foreign key for the second table. Why EF did not insert that constraint? Because we will violate always a constraint so we could not insert records on tables (also inside a transaction a reference key constraint can be violated).

Changing the configuration to HasRequired/WithRequiredDependent we obtain the table with the independent primary key will be the Emps table

modelBuilder.Entity<Dept>()
    .HasRequired(_ => _.Manager)
    .WithRequiredDependent(_ => _.Department);

This is the DDL generated

ExecuteNonQuery==========
CREATE TABLE [Depts] (
[DeptId] int not null
, [DeptName] text null
);
ALTER TABLE [Depts] ADD CONSTRAINT [PK_Depts_58ab8622] PRIMARY KEY ([DeptId])
ExecuteNonQuery==========
CREATE TABLE [Emps] (
[EmpId] int not null identity(1,1)
, [Name] text null
);
ALTER TABLE [Emps] ADD CONSTRAINT [PK_Emps_58ab8622] PRIMARY KEY ([EmpId])
ExecuteNonQuery==========
CREATE INDEX [IX_DeptId] ON [Depts] ([DeptId])
ExecuteNonQuery==========
ALTER TABLE [Depts] ADD CONSTRAINT [FK_Depts_Emps_DeptId] FOREIGN KEY ([DeptId]) REFERENCES [Emps] ([EmpId])

You can use other methods to obtain similar configurations. I dont show here every example but we can mix these configurations

HasOptional/WithRequired
HasOptional/WithOptionalDependent
HasOptional/WithOptionalPrincipal