linq filtering child collection

Question

I have read over a bunch of different topics on this, but i havent found what I am looking for.

I have an EF query that is this:

var query = this.ObjectContext.Questions
            .Include("AnswerKey").Include("QuestionTypes")
            .Where(o => o.SurveyQuestions.Any(o2 => o2.SurveyID == id));

This was working fine until i realized that i was not taking into account my Active Flag for the AnswerKey child collection. In other words, this query should load all questions that have a parent surveyid of 3(which it does)but only load AnswerKeys that have an active flag of true.

I have tried this:

var query = this.ObjectContext.AnswerKey
                .Include("Questions.QuestionTypes")
                .Where(ak =>
                    ak.Active == true &&
                    ak.Questions.SurveyQuestions.Any(sq => sq.SurveyID == 3) &&
                    ak.Questions.Active == true)
                    .AsEnumerable()
                    .Select(ak => ak.Questions).AsQueryable();

But it returns 1 question for each answerkey. So if a question has 4 answer it shows up 4 times...

How can i do this?

score 2 · Accepted Answer · answered Jun 01 '11 at 17:28

2

You could just use Distinct() at the end to filter out the duplicates:

  .AsEnumerable()
  .Select(ak => ak.Questions)
  .Distinct()
  .AsQueryable();

answered Jun 01 '11 at 17:28

BrokenGlass

158,293
28
286
335

score 1 · Answer 2 · answered Jun 01 '11 at 17:39

1

Brokenglass I will try your suggestion. And give you the credit if it works..

I also found this here after following another link on SO... and this appears to work as well but i need to verify it in my app.

answered Jun 01 '11 at 17:39

ecathell

1,030
13
25

1

you should go with the solution provided in the link you posted, this will be more efficient since you only materialize the entities that are truly needed. – BrokenGlass Jun 01 '11 at 17:47
i think yours is just as efficient. the same author also posts a similiar example here http://stackoverflow.com/questions/1535443/ef-query-with-conditinal-include/1535618 – ecathell Jun 01 '11 at 20:02

linq filtering child collection

2 Answers2