C# 使用 Entity Framework Fluent API 的一对一可选关系

One to one optional relationship using Entity Framework Fluent API

提问by ?lkay ?lknur

We want to use one to one optional relationship using Entity Framework Code First. We have two entities.

我们希望使用 Entity Framework Code First 来使用一对一的可选关系。我们有两个实体。

public class PIIUser
{
    public int Id { get; set; }

    public int? LoyaltyUserDetailId { get; set; }
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    public int Id { get; set; }
    public double? AvailablePoints { get; set; }

    public int PIIUserId { get; set; }
    public PIIUser PIIUser { get; set; }
}

PIIUsermay have a LoyaltyUserDetailbut LoyaltyUserDetailmust have a PIIUser. We tried these fluent approach techniques.

PIIUser可能有LoyaltyUserDetail但LoyaltyUserDetail必须有PIIUser。我们尝试了这些流畅的方法技巧。

modelBuilder.Entity<PIIUser>()
            .HasOptional(t => t.LoyaltyUserDetail)
            .WithOptionalPrincipal(t => t.PIIUser)
            .WillCascadeOnDelete(true);

This approach didn't create LoyaltyUserDetailIdforeign key in PIIUserstable.

这种方法没有LoyaltyUserDetailId在PIIUsers表中创建外键。

After that we tried the following code.

之后我们尝试了以下代码。

modelBuilder.Entity<LoyaltyUserDetail>()
            .HasRequired(t => t.PIIUser)
            .WithRequiredDependent(t => t.LoyaltyUserDetail);

But this time EF didn't create any foreign keys in these 2 tables.

但是这次EF没有在这两个表中创建任何外键。

Do you have any ideas for this issue? How can we create one to one optional relationship using entity framework fluent api?

你对这个问题有什么想法吗？我们如何使用实体框架 fluent api 创建一对一的可选关系？

采纳答案by Julie Lerman

EF Code First supports 1:1and 1:0..1relationships. The latter is what you are looking for ("one to zero-or-one").

EF Code First 支持1:1和1:0..1关系。后者是您正在寻找的（“一到零或一”）。

Your attempts at fluent are saying required on both endsin one case and optional on both endsin the other.

您对 fluent 的尝试是说在一种情况下两端都是必需的，而在另一种情况下两端都是可选的。

What you need is optionalon one end and requiredon the other.

您需要的一方面是可选的，另一方面是必需的。

Here's an example from the Programming E.F. Code First book

这是编程 EF Code First 一书中的示例

modelBuilder.Entity<PersonPhoto>()
.HasRequired(p => p.PhotoOf)
.WithOptional(p => p.Photo);

The PersonPhotoentity has a navigation property called PhotoOfthat points to a Persontype. The Persontype has a navigation property called Photothat points to the PersonPhototype.

该PersonPhoto实体有一个导航属性，称为PhotoOf指向一个Person类型。该Person类型有一个导航属性，称为Photo指向该PersonPhoto类型。

In the two related classes, you use each type's primary key, not foreign keys. i.e., you won't use the LoyaltyUserDetailIdor PIIUserIdproperties. Instead, the relationship depends on the Idfields of both types.

在两个相关的类中，您使用每种类型的主键，而不是外键。即，您不会使用LoyaltyUserDetailIdorPIIUserId属性。相反，关系取决于Id两种类型的字段。

If you are using the fluent API as above, you do not need to specify LoyaltyUser.Idas a foreign key, EF will figure it out.

如果您使用的是上述 fluent API，则不需要指定LoyaltyUser.Id为外键，EF 会计算出来。

So without having your code to test myself (I hate doing this from my head)... I would translate this into your code as

所以没有你的代码来测试自己（我讨厌从我的脑海里做这个）​​......我会把它翻译成你的代码

public class PIIUser
{
    public int Id { get; set; }    
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    public int Id { get; set; }
    public double? AvailablePoints { get; set; }    
    public PIIUser PIIUser { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<LoyaltyUserDetail>()
  .HasRequired(lu => lu.PIIUser )
  .WithOptional(pi => pi.LoyaltyUserDetail );
}

That's saying LoyaltyUserDetails PIIUserproperty is requiredand PIIUser's LoyaltyUserDetailproperty is optional.

也就是说 LoyaltyUserDetailsPIIUser属性是必需的，而 PIIUser 的LoyaltyUserDetail属性是可选的。

You could start from the other end:

你可以从另一端开始：

modelBuilder.Entity<PIIUser>()
.HasOptional(pi => pi.LoyaltyUserDetail)
.WithRequired(lu => lu.PIIUser);

which now says PIIUser's LoyaltyUserDetailproperty is optional and LoyaltyUser's PIIUserproperty is required.

现在说 PIIUser 的LoyaltyUserDetail属性是可选的，而 LoyaltyUser 的PIIUser属性是必需的。

You always have to use the pattern HAS/WITH.

你总是必须使用模式 HAS/WITH。

HTH and FWIW, one to one (or one to zero/one) relationships are one of the most confusing relationships to configure in code first so you are not alone! :)

HTH 和 FWIW，一对一（或一对零/一）关系是首先在代码中配置的最令人困惑的关系之一，因此您并不孤单！:)

回答by p.s.w.g

Try adding the ForeignKeyattribute to the LoyaltyUserDetailproperty:

尝试将ForeignKey属性添加到LoyaltyUserDetail属性：

public class PIIUser
{
    ...
    public int? LoyaltyUserDetailId { get; set; }
    [ForeignKey("LoyaltyUserDetailId")]
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
    ...
}

And the PIIUserproperty:

和PIIUser财产：

public class LoyaltyUserDetail
{
    ...
    public int PIIUserId { get; set; }
    [ForeignKey("PIIUserId")]
    public PIIUser PIIUser { get; set; }
    ...
}

回答by Frans Bouma

There are several things wrong with your code.

您的代码有几处错误。

A 1:1relationship is either: PK<-PK, where one PK side is also an FK, or PK<-FK+UC, where the FK side is a non-PK and has a UC. Your code shows you have FK<-FK, as you define both sides to have an FK but that's wrong. I recon PIIUseris the PK side and LoyaltyUserDetailis the FK side. This means PIIUserdoesn't have an FK field, but LoyaltyUserDetaildoes.

甲1:1关系可以是：PK <-PK，其中，一个PK侧也是FK，或PK <-fk + UC，其中FK侧是一个非PK和具有UC。您的代码显示您有FK<-FK，因为您定义双方都有 FK 但这是错误的。我侦察PIIUser是PK方面，LoyaltyUserDetail是FK方面。这意味着PIIUser没有 FK 字段，但LoyaltyUserDetail有。

If the 1:1relationship is optional, the FK side has to have at least 1 nullable field.

如果1:1关系是可选的，则 FK 端必须至少有 1 个可空字段。

p.s.w.g. above did answer your question but made a mistake that s/he also defined an FK in PIIUser, which is of course wrong as I described above. So define the nullable FK field in LoyaltyUserDetail, define the attribute in LoyaltyUserDetailto mark it the FK field, but don't specify an FK field in PIIUser.

上面的 pswg 确实回答了您的问题，但犯了一个错误，即他/她还在 PIIUser 中定义了 FK，这当然是我上面描述的错误。所以在 中定义可以为空的 FK 字段LoyaltyUserDetail，定义属性 inLoyaltyUserDetail将其标记为 FK 字段，但不要在 中指定 FK 字段PIIUser。

You get the exception you describe above below p.s.w.g.'s post, because no side is the PK side (principle end).

你会得到你在 pswg 的帖子下面描述的例外，因为没有一方是 PK 方（原则结束）。

EF isn't very good at 1:1's as it's not able to handle unique constraints.I'm no expert on Code first, so I don't know whether it is able to create a UC or not.

EF 不太擅长 1:1，因为它无法处理唯一约束。首先我不是Code专家，所以我不知道它是否能够创建一个UC。

(edit) btw: A 1:1B (FK) means there's just 1 FK constraint created, on B's target pointing to A's PK, not 2.

（编辑）顺便说一句：A 1:1B (FK) 意味着只创建了 1 个 FK 约束，在 B 的目标上指向 A 的 PK，而不是 2。

回答by jr from Yaoundé

Just do like if you have one-to-many relationship between LoyaltyUserDetailand PIIUserso you mapping should be

就像你之间有一对多的关系一样LoyaltyUserDetail，PIIUser所以你的映射应该是

modelBuilder.Entity<LoyaltyUserDetail>()
       .HasRequired(m => m.PIIUser )
       .WithMany()
       .HasForeignKey(c => c.LoyaltyUserDetailId);

EF should create all foreign key you need and just don't care about WithMany!

EF 应该创建您需要的所有外键，并且不关心 WithMany！

回答by pampi

public class User
{
    public int Id { get; set; }
    public int? LoyaltyUserId { get; set; }
    public virtual LoyaltyUser LoyaltyUser { get; set; }
}

public class LoyaltyUser
{
    public int Id { get; set; }
    public virtual User MainUser { get; set; }
}

        modelBuilder.Entity<User>()
            .HasOptional(x => x.LoyaltyUser)
            .WithOptionalDependent(c => c.MainUser)
            .WillCascadeOnDelete(false);

this will solve the problem on REFERENCEand FOREIGN KEYS

这将解决REFERENCE和FOREIGN KEYS上的问题

when UPDATINGor DELETINGa record

当UPDATING或DELETING记录

回答by skjagini

The one thing that is confusing with above solutions is that the Primary Key is defined as "Id"in both tables and if you have primary key based on the table name it wouldn't work, I have modified the classes to illustrate the same, i.e. the optional table shouldn't define it's own primary key instead should use the same key name from main table.

与上述解决方案混淆的一件事是，主键在两个表中都定义为“ Id”，如果您有基于表名的主键，它将不起作用，我已经修改了类来说明相同的情况，即可选表不应该定义它自己的主键，而是应该使用与主表相同的键名。

public class PIIUser
{
    // For illustration purpose I have named the PK as PIIUserId instead of Id
    // public int Id { get; set; }
    public int PIIUserId { get; set; }

    public int? LoyaltyUserDetailId { get; set; }
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    // Note: You cannot define a new Primary key separately as it would create one to many relationship
    // public int LoyaltyUserDetailId { get; set; }

    // Instead you would reuse the PIIUserId from the primary table, and you can mark this as Primary Key as well as foreign key to PIIUser table
    public int PIIUserId { get; set; } 
    public double? AvailablePoints { get; set; }

    public int PIIUserId { get; set; }
    public PIIUser PIIUser { get; set; }
}

And then followed by

然后紧随其后

modelBuilder.Entity<PIIUser>()
.HasOptional(pi => pi.LoyaltyUserDetail)
.WithRequired(lu => lu.PIIUser);

Would do the trick, the accepted solution fails to clearly explain this, and it threw me off for few hours to find the cause

可以解决问题，公认的解决方案未能清楚地解释这一点，这让我花了几个小时才找到原因