Perform LINQ in Entity Framework core to load sub-related entities Data

.net-core entity-framework-core linq

Question

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

Question

public class QuestionDataModel
{
    public QuestionDataModel()
    {
        Answers = new HashSet<AnswerDataModel>();
    }

    public Guid Id { get; set; }
    public virtual ICollection<AnswerDataModel> Answers { get; set; }

}

Answer

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

}

AnswerStatusType

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,

linq

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

Configuration Classes

 and configuration classes as

Question Config

  public void Configure(EntityTypeBuilder<QuestionDataModel> builder)
    {

        builder.ToTable("Questions");
        builder.HasKey(question => question.Id);
        builder.HasMany(question => question.Answers);
    }

Answer Config

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

AnswerStatus COnfig

    public void Configure(EntityTypeBuilder<AnswerStatusTypeDataModel> builder)
    {
        builder.ToTable("AnswerStatusTypes");
        builder.HasKey(answerStatusType => answerStatusType.Id);
        builder.HasMany(answerStatusType => answerStatusType.Answers);
    }
1
0
2/11/2018 9:20:31 PM

Popular Answer

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!

2
2/12/2018 1:45:25 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow