Inner query in Linq

Question

How to write a linq query to retreive questions from questions table which is not attempt by user before.

question table

create table questions
(
  questionID int,
  question varchar,
  primary key(questionID)
);

create table result
{
  id int,
  answered_by varchar,
  questionid int,
  answer varchar,
  primary key (id),
  foreign key ( questionid) references question(questionID)
);

What is your dataContext.Are you using entityframework for this ? — Thilina H, Sep 25 '13 at 11:25
http://stackoverflow.com/questions/183791/how-would-you-do-a-not-in-query-with-linq check this — Swapnil Patil, Sep 25 '13 at 11:27

score 2 · Answer 1 · answered Sep 25 '13 at 11:32

2

Not sure what are you using.

But can suggest something like below using class level.

Assuming you have load all the data from database to to lists.

var list= questionsList.Where(i => resultList.All(p => p.questionid != i.questionid));

answered Sep 25 '13 at 11:32

huMpty duMpty

14,346
14
60
99

score 0 · Answer 2 · edited Sep 25 '13 at 11:35

0

from QUESTION in QUESTIONS
join
ANSWER in RESULT
on
QUESTION.questionID equlas ANSWER.questionid
where
ANSWER.answer!=NULL
select {QUESTION.questionID, ANSWER.answered_by, ANSWER.questionid, ANSWER.answer}

edited Sep 25 '13 at 11:35

huMpty duMpty

14,346
14
60
99

answered Sep 25 '13 at 11:34

Kashif Junaid

21
1
5

score 0 · Answer 3 · answered Sep 25 '13 at 11:53

0

For people who start with a group of in-memory objects and are querying against a database, I've found this to be the best way to go:

var itemIds = resultList.Select(x => x.questionid ).ToArray();
var results = questionsList.Where(x => !itemIds.Contains(x.questionid ));

**This produces a nice WHERE ... IN (...) clause in SQL.**

answered Sep 25 '13 at 11:53

Thilina H

5,754
6
26
56

I think you can do the same like this with single line `var x = questionsList.Where(i => !resultList.Any(p => p.questionid == i.questionid ));` – huMpty duMpty Sep 25 '13 at 12:09

Inner query in Linq

3 Answers3