ef code first - Entity Framework Core - get rid of duplicate id of inherited object

Question

I'm using PostgreSQL database with the EF Core (with the Table-Per-Type inheritance).

I have questions and question items (in this case, options for answering a question). Any question must have an Id, Text and Cost. Question item must have an Id, Text and QuestionId (id of the question to which it belongs). Some questions (e.g. MultipleChoiceQuestion) contain question items and some don't (e.g. TextQuestion).

Each QuestionItem has a QuestionId and MultipleChoiceQuestionAnswerOption can only be used with MultipleChoiceQuestion. So I want to have a navigation property inside the MultipleChoiceQuestionAnswerOption that refers the corresponding MultipleChoiceQuestion. I know that if MultipleChoiceQuestionAnswerOption can only be used with MultipleChoiceQuestion, then MultipleChoiceQuestionAnswerOption.QuestionId always references the Question which is actually MultipleChoiceQuestion. But I don't know how to set this up with EF Core.

I want to have entities like the following:

public class abstract Question
{
    public Guid Id { get; set; }
    public string Text { get; set; }
    public int Cost { get; set; }
    public ICollection<QuestionItem> Items { get; set; }
}

public class TextQuestion : Question
{
    public string CorrectAnswer { get; set; }
}

public class MultipleChoiceQuestion : Question
{
    public ICollection<MultipleChoiceQuestionAnswerOption> AnswerOptions { get; set; }
}

public abstract class QuestionItem
{
    public Guid Id { get; set; }
    public string Text { get; set; }
    public Guid QuestionId { get; set; }
    public Question Question { get; set; }
}

public class MultipleChoiceQuestionAnswerOption : QuestionItem
{
    public bool IsCorrect { get; set; }
    public MultipleChoiceQuestion MultipleChoiceQuestion { get; set; }
}

With the following relation configurations:

questionBuilder
    .HasMany(question => question.Items)
    .WithOne(questionItem => questionItem.Question)
    .HasForeignKey(questionItem => questionItem.QuestionId);

multipleChoiceQuestionBuilder
    .HasMany(question => question.AnswerOptions)
    .WithOne(answerOption => answerOption.MultipleChoiceQuestion)
    .HasForeignKey(answerOption => answerOption.QuestionId);

I've tried leaving it like this, but when staring the application I get this error message:

The foreign key {'QuestionId'} on the entity type 'MultipleChoiceQuestionAnswerOption' targeting 'MultipleChoiceQuestion' cannot be represented in the database. Either the properties {'QuestionId'} aren't mapped t o table 'multiple_choice_question_answer_options' or the principal properties {'Id'} aren't mapped to table 'multiple_choice_questions'. All foreign key properties must map to the table that the dependent type is mapped to and a ll principal properties must map to a single table that the principal type is mapped to.

My guess is that you need to provide a separate foreign key property for each navigation property. But in this case, the interface of the MultipleChoiceQuestionAnswerOption entity will look something like this:

Guid Id { get; set; }
string Text { get; set; }
bool IsCorrect { get; set; }

Guid QuestionId { get; set; }
Guid MultipleChoiceQuestionId { get; set; }

Question Question { get; set; }
MultipleChoiceQuestion MultipleChoiceQuestion { get; set; }

And the values of the QuestionId and MultipleChoiceQuestionId fields will always be the same (since MultipleChoiceQuestionAnswerOption can only be used with MultipleChoiceQuestion). I want to get rid of this duplication, but not sure how exactly.

What configuration should be applied to reuse the QuestionId field for both the base Question type and the derived MultipleChoiceQuestion type?

question from:https://stackoverflow.com/questions/65950734/entity-framework-core-get-rid-of-duplicate-id-of-inherited-object

Answer 1

Waitting for answers