I am working on .NET Core application with Entity Framework Core. I have three tables Questions, Answer and AnswerType so schema is as
question <-- 1:* --> Answers <-- 1:1--> AnswerTypes
I need to run query that return questions with ICollection of Answer , further Answer with AnswerType
public class QuestionDataModel
{
public QuestionDataModel()
{
Answers = new HashSet<AnswerDataModel>();
}
public Guid Id { get; set; }
public virtual ICollection<AnswerDataModel> Answers { get; set; }
}
public class AnswerDataModel
{
public AnswerDataModel()
{
}
public Guid Id { get; set; }
public Guid QuestionId { get; set; }
public virtual QuestionDataModel Question { get; set; }
public string Value { get; set; }
public Guid AnswerStatusTypeId { get; set; }
public virtual AnswerStatusTypeDataModel AnswerStatusType { get; set; }
}
public class AnswerStatusTypeDataModel
{
public AnswerStatusTypeDataModel()
{
Answers = new HashSet<AnswerDataModel>();
}
public Guid Id { get; set; }
public string Name { get; set; }
public virtual ICollection<AnswerDataModel> Answers { get; set; }
}
I have tried nested join to get AnswerStatusType of each answer in collection but getting error "invalid anonymous type member declared, anonymous type member must be declared with a member assignment, simple name or member access". This error appears in 2nd nested join in following code,
var query3 = Context.Questions.Join(Context.Answers,
question => question.Id,
answer => answer.QuestionId,
(question, answer) => new
{
question.Id,
question.Title,
question.Answers.Join(Context.AnswerStatusTypes,
answer => answer.AnswerStatusTypeId,
answerStatus => answerStatus.Id,
(answers, answerStatus) => new
{
answerStatus
})
}
);
and configuration classes as
public void Configure(EntityTypeBuilder<QuestionDataModel> builder)
{
builder.ToTable("Questions");
builder.HasKey(question => question.Id);
builder.HasMany(question => question.Answers);
}
public void Configure(EntityTypeBuilder<AnswerDataModel> builder)
{
builder.ToTable("Answers");
builder.HasKey(answer => answer.Id);
builder
.HasOne(answer => answer.Question)
.WithMany(question => question.Answers)
.HasForeignKey(answer => answer.QuestionId);
builder
.HasOne(answer => answer.AnswerStatusType)
.WithMany(answerType => answerType.Answers)
.HasForeignKey(answer => answer.AnswerStatusTypeId);
}
public void Configure(EntityTypeBuilder<AnswerStatusTypeDataModel> builder)
{
builder.ToTable("AnswerStatusTypes");
builder.HasKey(answerStatusType => answerStatusType.Id);
builder.HasMany(answerStatusType => answerStatusType.Answers);
}
Your entity configurations look correct to me.
As @Ivan Stoev pointed out,
var questions = context.Questions
.Include(x => x.Answers)
.ThenInclude(x => x.AnswerStatusType)
// Now since we have the AnswerStatusType loaded we can do something like this as well.
//.Where(x => x.Answers.Conatins(a => a.AnswerStatusType.Name == "Some Status Name"))
.ToList();
This should do!