How to add where clause to ThenInclude
Solution 1
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
Solution 2
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();
Admin
Updated on July 13, 2022Comments
-
Admin almost 2 years
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?
-
Yashveer Singh about 7 yearscan you tell me what is your query what you want to find ?
-
Admin about 7 yearsEvery questionnaire has questions and questions has answers, and i need to filter these question answers by userId.
-
Yashveer Singh about 7 yearsI 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();
-