Entity Framework Nested Query Select() Problem

Question

I am new to the entity framework and trying to get data using foreign key from Answers table but I am getting error

The nested query is not supported. Operation1='Case' Operation2='Collect'

p.Answers.Count() or p.Answers.SingleOrDefault().correct_answeras string works fine but p.Answers.Select(c => c.correct_answer).ToList() throwing nested query error

I want to keep it in one query because there could be thousands of questions so I don't want to check answers to the separate queries. Following is my code.

return db.Questions.Where(p => p.q_id == q_id).Select(p => new QuestionViewModel
{
   q_id = p.q_id,
   q_text = p.q_text,
   q_answer = p.Answers.Count() > 0 ? p.Answers.Select(c => c.correct_answer).ToList() : null
}).OrderBy(x => x.q_id).ToList();

ViewModel

public class QuestionViewModel
{
    public long q_id { get; set; }
    public string q_text { get; set; }
    public List<string> q_answer { get; set; }
}

Ivan Stoev · Accepted Answer · 2019-12-30 15:56:20Z

2

The exception is telling you that EF does not support conditional subquery, implied by expressions like this:

p.Answers.Count() > 0 ? p.Answers.Select(c => c.correct_answer).ToList() : null

So simply remove the conditional operator:

q_answer = p.Answers.Select(c => c.correct_answer).ToList()

In case there are no related answers for question, q_answer will be populated with empty list rather than null, which is the normal (expected) behavior for collection type objects.

answered Dec 30, 2019 at 15:56

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aadi1295 Over a year ago

Thanks, I didn't know EF doesn't support conditional subquery. Without conditional subquery, it's working fine. Thanks again

Collectives™ on Stack Overflow

Entity Framework Nested Query Select() Problem

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related