How to add where clause to ThenInclude

Question

I have 3 entities:

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 answers but now i want to retrieve filtered questionnaire with questions and its answers. So i wrote linq for that, but it throws me an error, is there anything i do wrong? here is the example:

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

And i am getting

Message = "The property expression 'q => {from Answer a in q.Answers where Equals([a].UserId, __userId_0) select [a]}' is not valid. The expression should represent a property access: 't => t.MyProperty'.

Any ideas how to solve this problem?

Every questionnaire has questions and questions has answers, and i need to filter these question answers by userId. — , Feb 06 '17 at 08:38
I changed your model class to have a navigation property of Question in your answer . var questionnaire = ctx.Answers.Include(q=>q.Question).Where(a =>a.UserId=="1").ToList(); — Yashveer Singh, Feb 06 '17 at 08:55

score 14 · Answer 1 · edited Jul 09 '21 at 01:57

14

Filtering in Include or ThenInclude is not supported. Create projection by using 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);

There is a github issue about this problem: https://github.com/aspnet/EntityFramework/issues/3474

edited Jul 09 '21 at 01:57

Pang

9,564
146
81
122

answered Feb 06 '17 at 08:55

Pawel Maga

5,428
3
38
62

This is no longer correct. .ThenInclude().Where() is now included at least on the first level – Black-Pawn-C7 Dec 15 '22 at 00:22

score 1 · Answer 2 · answered Feb 06 '17 at 08:58

I think you need to have a navigation property of question in your answer because answer should have an question Id . You have this FK already you just need a nav property

Your model class look like this

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

and query like this

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

Ahmet Arslan · Answer 3 · 2019-04-05T14:36:28.390

0

You can have navigation properties filtered on memory:

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

edited Apr 05 '19 at 14:36

answered Apr 05 '19 at 13:26

Ahmet Arslan

5,380
2
33
35

How to add where clause to ThenInclude

3 Answers3

Linked