How to Solve Combined One-to-One and One-to-Many Relationships in EF 5 Code First
I am using Entity Framework 5 and Code First.
I have two Domain Objects Question and Answer for a quiz application. One question has several possible answers. The question also has one correct answer, which must refer to one of the possible answers. I am experiencing some problems with the combination of one-to-many and one-to-one relationship between entities. See Q1 and Q2 .
This is the code for objects:
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; }
}
Q1: What should I do to insert the Question object and refer to the answers (via the PossibleAnswers property) in only one backward direction in the db (for example, one call to the SaveChanges context)? The error I get when I save the Q&A without adding the answers first:
Unable to determine a valid ordering for dependent operations. Dependencies can exist due to foreign key constraints, model requirements, or store-stored values.
To solve this problem, I tried the following using the fluent API to get the answers to be added before the Questions when you do it all with just one call to objectcontexts SaveChanges:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Question>()
.HasOptional(q => q.CorrectAnswer)
.WithRequired();
base.OnModelCreating(modelBuilder);
}
However, this led me to another error:
Conflicting changes found. This can happen when trying to insert multiple objects with the same key.
Am I on the right track with the free API approach for Q1? Why the error message?
Q2 : When deleting a question, I understand that there will be an error, since the question cannot be deleted before the answers and vice versa. How can I solve this? For example, it is assumed that WillCascadeOnDelete should be specified for both Question.CorrectAnswer and Question.PossibleAnswers?
source to share
For both Q1 and Q2 you will need two calls per round / two to SaveChanges
(except perhaps solving the stored procedure problem).
Q1: The first call is Question.CorrectAnswer
set to null
and the second call is set CorrectAnswer
to one of the stored responses.
Q2: The first call setup Question.CorrectAnswer
is null
, and the second call deletes Question
and associated responses with cascading delete enabled.
If you're not so worried about two rounds, but more about two transactions corresponding to two calls SaveChanges
, you can SaveChanges
manually wrap the entire operation, including the two calls , into one transaction. (Example: EF: How do I call SaveChanges twice inside a transaction? )
On a one-to-one relationship: Although from a business perspective the relationship for CorrectAnswer
is one-to-one , it is difficult or even impossible to model it as a one-to-one relationship with EF.
The problem is that EF does not support one-to-one foreign key associations, i.e. a relationship in which the foreign key (CorrectAnswerId or so) has a unique constraint. It only supports general one-to-one primary key associations, where the dependent's primary key ( Question
) is the foreign key (for Question.CorrectAnswer
) for the principal ( Answer
) at the same time. Your Free Code is the configuration for such a common primary key relationship. But this would mean that the only valid one CorrectAnswer
is Answer
that has the same primary key value as Question
. While this is theoretically possible to achieve (your table Answer
has more records than the tableQuestion
), most likely, you will not need to manually use keys to use auto-generated keys. Changing CorrectAnswers
from one Answer
to the other would be impossible. So, shared primary keys are not appropriate for your model in my opinion.
The best solution would be to remove your Fluent mapping. The result is a one-to-many relationship with a null foreign key for CorrectAnswer
the table Question
. From a database perspective, this means the same Answer
could be CorrectAnswer
for many Question
, which is probably pointless in your business logic, because each question has its own unique set of answers, and the two questions never share the same answer. But you can "hide" one-to-many relationships from your business logic simply by not adding the inverse collection property (for example QuestionsThisIsTheCorrectAnswerFor
) to Answer
. While not ideal for business constraints, it technically works seamlessly.
More on the challenges of a one-to-one relationship with EF can be found in these blog posts:
source to share