使用实体框架Fluent API的一对一可选关系

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

我们希望首先使用一个到一个可选关系使用实体框架代码。我们有两个实体。

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

PIIUser may have a LoyaltyUserDetail but LoyaltyUserDetail must 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 LoyaltyUserDetailId foreign key in PIIUsers table.

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

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创建一个到一个可选关系?

6 个解决方案

#1

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

EF代码首先支持1:1和1:0。1的关系。后者是您正在寻找的(“一个到零或一个”)。

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

你想说流利的意思是在一种情况下两端都需要，在另一种情况下两端都可选。

What you need is optional on one end and required on the other.

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

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

这里有一个来自编程E.F.代码第一本书的例子

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

The PersonPhoto entity has a navigation property called PhotoOf that points to a Person type. The Person type has a navigation property called Photo that points to the PersonPhoto type.

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 LoyaltyUserDetailId or PIIUserId properties. Instead, the relationship depends on the Id fields of both types.

在两个相关的类中，使用每种类型的主键，而不是外键。即。，您不会使用LoyaltyUserDetailId或PIIUserId属性。相反，关系依赖于这两种类型的Id字段。

If you are using the fluent API as above, you do not need to specify LoyaltyUser.Id as 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 PIIUser property is required and PIIUser's LoyaltyUserDetail property is optional.

这就是说，需要使用LoyaltyUserDetails PIIUser属性，而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 LoyaltyUserDetail property is optional and LoyaltyUser's PIIUser property 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, 1到1(或1到0 / 1)关系是在代码中首先配置的最令人困惑的关系之一，因此您并不孤单!:)

#2

Just do like if you have one-to-many relationship between LoyaltyUserDetail and PIIUser so 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应该创造出所有你需要的外键，而只是不关心!

#3

There are several things wrong with your code.

您的代码有几个问题。

A 1:1 relationship 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 PIIUser is the PK side and LoyaltyUserDetail is the FK side. This means PIIUser doesn't have an FK field, but LoyaltyUserDetail does.

一个1:1的关系是:PK<-PK，其中一个PK边也是FK; PK<-FK+UC，其中FK边是非PK，有UC。您的代码显示您有FK<-FK，因为您定义了两边都有FK，但这是错误的。我recon PIIUser是PK端，LoyaltyUserDetail是FK端。这意味着PIIUser没有FK字段，但LoyaltyUserDetail有。

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

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

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 LoyaltyUserDetail to mark it the FK field, but don't specify an FK field in PIIUser.

上面的p.s.w.g的确回答了你的问题，但是他在PIIUser中定义了FK，这当然是错误的。因此，在LoyaltyUserDetail中定义可空的FK字段，在LoyaltyUserDetail中定义属性，以标记它的FK字段，但不要在PIIUser中指定FK字段。

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

你会得到你在p.s.w.g下面描述的例外。因为没有一方是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，因为它不能处理唯一的约束。我不是第一个代码专家，所以我不知道它是否能够创建UC。

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

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

#4

Try adding the ForeignKey attribute to the LoyaltyUserDetail property:

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

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

And the PIIUser property:

和PIIUser属性:

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

#5

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 REFERENCE and FOREIGN KEYS

这将解决参考和外键的问题

when UPDATING or DELETING a record

更新或删除记录时

#6

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

能不能解决这个问题，公认的解决方案不能很清楚地解释这个问题，而我却花了几个小时才找到原因

#1