实体与外键交叉引用时的代码优先迁移 [英] Code First migration when entities have cross references with foreign keys

问题描述

我有相互引用的模型:

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

当我调用 Add-Migration 时，出现错误:

When I call Add-Migration, I get error:

类型App.Dept"的属性Manager"上的 ForeignKeyAttribute 无效.在依赖类型App.Emp"上找不到外键名称ManagerId".Name 值应该是逗号分隔的外键属性名称列表.

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:隐式可选Manager并没有解决问题:

UPD: Implicit optional Manager does not solve the problem:

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

UPD2: Dept:Emp 关系为 1:0..1

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

UPD3:可能会在 Dept 模型中添加另一个关系，但它也会是 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; }

这不是一对多的关系:一个部门有零个或一个经理，零个或一个 CTO.目前我只有一个关系，但我想将字段命名为 ManagerId，而不是 EmpId.

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:从我的问题开始的架构与两个主/外键关系(Dept.DeptId/Emp.DeptId、Emp.EmpId/Dept.ManagerId)在纯 SQL 中工作.我知道有附加表或没有外键的解决方法，但我需要一个答案如何在上面制作工作模式或为什么它在 EF 中不起作用.

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.

推荐答案

你主要有三种方式来配置1-1关系(你的错误情况是第3种解释的情况).

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).

在您的情况下，您只需使用 ComplexType 属性标记实体之一

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

标准外键
第二种方法是使用标准外键.该模型可以在两个类上都有导航属性，有 2 个具有 independent 主键的表，但只有 1 个表具有另一个表的外键(您在问题上写了有关此配置的信息).您获得此配置覆盖 OnModelCreating.使用这种方式，您可以使用 fluent API 进行多种配置.主要选项是 EF 应该在哪里插入外键.在每个配置中都必须有 Map 方法(我以第三种方式解释没有 Map 方法会发生什么)

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)

模型总是这个

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)

WithRequiredPrincipal (1-1)

来自 MSDN:将关系配置为必需:在关系的另一端没有导航属性的必需.> 配置的实体类型将是关系中的主体.关系目标的实体类型将是从属的，并包含一个外键 > 主体.

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"));

这是生成的 DDL

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)

WithRequiredDependent (1-1)

来自 MSDN:将关系配置为必需:在关系的另一端没有导航属性的必需.[对我来说不清楚这个解释，反正真实的行为见下文]

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"));

这是生成的 DDL

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)

WithOptional (1-0..1)

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

这是生成的 DDL

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 默认 0..1-1 1-0..1 1-1 配置
这就是 EF 解释您的配置的方式.在这种情况下，EF 生成 2 个具有 dependent 主键的表.在一个表上，有一个独立的主键(在您的情况下为 identity(1,1))，而在另一个表上，您有一个主键，它也是外键.这是默认配置.这是在两个表上都有外键的唯一方法(不是 2 个约束，没有办法有 2 个循环约束，见下文)

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

这是生成的 DDL

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])

这应该是 1-1 的关系，但如果我们看起来更好，就会缺少一个约束.Dept 表的主键应该是第二个表的外键.为什么 EF 没有插入该约束?因为我们总是会违反一个约束，所以我们不能在表上插入记录(在事务中也可能违反引用键约束).

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).

把配置改成HasRequired/WithRequiredDependent我们得到独立主键的表就是Emps表

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

这是生成的 DDL

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

这篇关于实体与外键交叉引用时的代码优先迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！