How to add a where clause to ThenInclude

Question

I have 3 objects:

Questionnaire.cs:

public class Questionnaire
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Question> Questions { get; set; }
}

Question.cs:

public class Question
{
    public int Id { get; set; }
    public string Text { get; set; }
    public ICollection<Answer> Answers { get; set; }
}

and Answer.cs:

public class Answer
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public string TextAnswer { get; set; }
}

So, I saved the questionnaire with the answers, but now I want to get a filtered questionnaire with questions and answers. So, I wrote linq for this, but it gives me an error, is there something I am doing wrong? here is an example:

questionnaire = _context.Questionnaires.Include(qn => qn.Questions)
.ThenInclude(question => question.Answers.Where(a => a.UserId == userId))
.FirstOrDefault(qn => qn.Id == questionnaireId);

And I get

Message = "Property Expression" q => {from answer a to q. where Equals ([a] .UserId, __userId_0) select [a]} 'is invalid. the expression should represent access to the properties: 't => t.MyProperty'.

Any ideas how to solve this problem?

Answer 1

Include IncludeThen . Select:

questionnaire = _context.Questionnaires
    .Select(n => new Questionnaire
    {
        Id = n.Id,
        Name = n.Name,
        Questions = n.Questions.Select(q => new Question
        {
           Id = q.Id,
           Text = q.Text,
           Answers = q.Where(a => a.UserId == userId).ToList()
        }).ToList()
    })
    .FirstOrDefault(qn => qn.Id == questionnaireId);

GitHub : https://github.com/aspnet/EntityFramework/issues/3474

Answer 2

, , Id. FK, nav

public class Answer
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public string TextAnswer { get; set; }
    // added in model
    public Question Question { get; set; }
} 

  var answers = ctx.Answers.Include(q=>q.Question).Where(a =>a.UserId=="1").ToList();

Answer 3

,

var result =_context.Questionnaires.
    .Where(q => q.Question.Any(b => b.Answer.Userid == userid))

Answer 4

, :

var questionnaire= _context.Questionnaire.FirstOrDefault(qn => qn.Id == questionnaireId);

questionnaire.Answers = _context.Entry(questionnaire)
 .Collection(b => b.Answers )
 .Query()
 .Where(a => a.UserId == userId).ToList();