您的位置: 首页  >  技术问答  >  如何在 EF 5 Code First 中解决组合一对一和一对多关系

如何在 EF 5 Code First 中解决组合一对一和一对多关系

分类: 技术问答 • 2022-04-01 20:28:18

如何在 EF 5 Code First 中解决组合一对一和一对多关系

问题描述：

我使用 Entity Framework 5 和 Code First.

I´m using Entity Framework 5 and Code First.

我有两个域实体 Question 和 Answer 用于测验应用程序.一个问题有几个可能的答案.一个问题也有一个正确答案，该答案应参考可能的答案之一.我在 to 实体之间的一对多和一对一关系的组合中遇到了一些问题.请参阅第一季度和第二季度.

I have two domain entities Question and Answer for a quiz application. One question has several possible answers. A question also has one correct answer which should reference one of the possible answers. I am experiencing some issues with the combination of a one-to-many and one-to-one relationship between the to entities. See Q1 and Q2.

这是实体的代码:

public class Question
{
    public virtual int Id { get; set; }
    [Required]
    public virtual string Text { get; set; }

    [InverseProperty("Question")] 
    public virtual ICollection<Answer> PossibleAnswers { get; set; }
    public virtual Answer CorrectAnswer { get; set; }        

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public virtual DateTime? UpdateStamp { get; set; }
}

public class Answer
{
    public virtual int Id { get; set; }
    [Required]
    public virtual string Text { get; set; }

    [ForeignKey("QuestionId")]
    public virtual Question Question { get; set; }
    public virtual int QuestionId { get; set; }
}

问题 1: 我应该怎么做才能在一次到数据库的往返(例如对上下文 SaveChanges 的一次调用)中插入问题对象和引用的答案(通过属性可能的答案)?当我保存问题和答案而不先添加答案时出现的错误是:

Q1: What should I do to be able to insert the Question object and referenced Answers (thru property PossibleAnswers) in just one roundtrip to the db (eg one call to the contexts SaveChanges)? The error I get when I save the Questions and Answers without adding the Answers first is:

无法确定相关操作的有效顺序.由于外键约束、模型要求或存储生成的值，可能存在依赖关系.

Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.

为了解决这个问题，我尝试了以下使用 fluent API 来获取要在问题之前添加的答案，只需一次调用 objectcontexts SaveChanges:

To solve that problem I tried the following using fluent API to get the Answers to be added prior to Questions when doing it all with just one call the objectcontexts SaveChanges:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Question>()
            .HasOptional(q => q.CorrectAnswer)
            .WithRequired();

        base.OnModelCreating(modelBuilder);
    }

然而，这又导致了我的另一个错误:

However, that lead me to another error:

检测到冲突的更改.尝试插入具有相同键的多个实体时可能会发生这种情况.

Conflicting changes detected. This may happen when trying to insert multiple entities with the same key.

我在 Q1 的流畅 API 方法上是否走在正确的道路上?为什么会出现错误信息?

Am I on the right path with the fluent API approach for Q1? Why the error message?

Q2:删除问题时我意识到会出错，因为问题不能在答案之前删除，反之亦然.我该如何解决这个问题?例如，是否应该在 Question.CorrectAnswer 和 Question.PossibleAnswers 上指定 WillCascadeOnDelete?

Q2: When deleting a question I realize that there will be an error since the question cannot be deleted before the answers and vice versa. How do I solve this? For instance, is WillCascadeOnDelete supposed to be specified on both Question.CorrectAnswer and Question.PossibleAnswers?

答

对于 Q1 和 Q2 的问题，您需要两次往返/两次调用 SaveChanges(除了使用 Stored程序可能).

For both your questions Q1 and Q2 you will need two roundtrips/two calls to SaveChanges (aside from solving the problem with a Stored Procedure maybe).

Q1:第一个调用将 Question.CorrectAnswer 设置为 null，第二个调用将 CorrectAnswer 设置为存储的答案之一.

Q1: The first call having Question.CorrectAnswer set to null and a second call that sets the CorrectAnswer to one of the stored answers.

Q2:第一个调用将 Question.CorrectAnswer 设置为 null，第二个调用删除 Question 和启用级联删除的相关答案.

Q2: The first call setting Question.CorrectAnswer to null and the second call deletes the Question and the related answers with enabled cascading delete.

如果您不太担心两次往返，而是更担心与两个 SaveChanges 调用相对应的两个事务，您可以包装整个操作，包括两个 SaveChanges 调用变成一个单一的手动事务.(例如:EF:如何在内部调用 SaveChanges 两次交易?)

If you are not so worried about the two roundtrips but more about the two transactions corresponding to the two SaveChanges calls you can wrap the whole operation including the two SaveChanges calls into a single manual transaction. (Example: EF: How do I call SaveChanges twice inside a transaction?)

关于一对一关系:虽然从业务角度来看，CorrectAnswer 的关系是一对一的，但很难甚至不可能将其建模为一对一与英孚的关系.

About the one-to-one relationship: Although from business perspective the relationship for the CorrectAnswer is one-to-one it is hard or even impossible to model it as a one-to-one relationship with EF.

问题是 EF 不支持外键一对一关联，即外键(CorrectAnswerId 左右)具有唯一约束的关系.它仅支持共享主键一对一关联，其中依赖项 (Question) 的主键是主体 (Question.CorrectAnswer) 的外键 (Answer) 同时.您的 Fluent 代码是这种共享主键关联的配置.但这意味着唯一有效的CorrectAnswer 是与Question 具有相同主键值的Answer.虽然这在理论上是可以实现的(您的 Answer 表比 Question 表有更多的记录)，但很可能需要不使用自动生成的密钥，而是手动提供密钥.将 CorrectAnswers 从一个 Answer 更改为另一个是不可能的.因此，我认为共享主键不适合您的模型.

The problem is that EF does not support foreign key one-to-one associations, i.e. relationships where the foreign key (CorrectAnswerId or so) has a unique constraint. It only supports shared primary key one-to-one associations where the primary key of the dependent (Question) is the foreign key (for Question.CorrectAnswer) to the principal (Answer) at the same time. Your Fluent code is the configuration for such a shared primary key association. But it would mean that the only valid CorrectAnswer is the Answer that has the same primary key value as the Question. While this is theoretically possible to achieve (your Answer table has more records than the Question table) it would most likely require to not use autogenerated keys but supply the keys manually. Changing the CorrectAnswers from one Answer to the other would be impossible. So, shared primary keys are not suited for your model in my opinion.

更好的解决方案是删除您的 Fluent 映射.结果将是与Question 表中CorrectAnswer 的可为空外键的一对多关系.从数据库的角度来看，这意味着相同的 Answer 可以是许多 Question 的 CorrectAnswer，这在您的业务逻辑中可能是无稽之谈，因为每个问题都有它有自己独特的一组答案，两个问题永远不会共享相同的答案.但是，您可以通过不向 Answer 添加逆向集合属性(如 QuestionsThisIsTheCorrectAnswerFor)来从业务逻辑中隐藏"这种一对多关系.虽然它没有完美地模拟业务约束，但它在技术上可以正常工作.

The better solution would be to remove your Fluent mapping. The result would be a one-to-many relationship with a nullable foreign key for CorrectAnswer in the Question table. From database perspective it means that the same Answer can be the CorrectAnswer for many Questions which is probably nonsense in your business logic because every question has its own unique set of answers and two questions never share the same answer. But you can "hide" this one-to-many relationship from your business logic by just not adding an inverse collection property (like QuestionsThisIsTheCorrectAnswerFor) to Answer. Although it does not model the business constraint perfectly it works technically without problems.

有关与 EF 建立一对一关系的困难的更多信息，请参阅以下博文:

More about the difficulties of one-to-one relationships with EF can be found in these blog posts:

相关推荐