Linq-to-sql Not Contains or Not in?

Question

I'm building a poll widget. I've 2 tables, call them Polls and PollsCompleted. I need to do a linq query to get all the Polls that do not exist for a given user in PollsCompleted.

I have the following sets:

For Polls Where Active == True

For PollsCompleted Where UserId == ThisUserId Where PollId = Polls.Id

Now I need to get all Polls that do not exist in PollsCompleted. I need an example for this using either a single or multiple queries. I've tried to break it down into 2 queries.

Basically, I've 2 IQueryables of type T and T1. I want to take all T's where T.ID does not exist in T1.ParentId.

Answer 1

Use Except. That will work in this case.

For your reference Enumerable.Except Method

Answer 2

T.Where(x => ! T1.Select(y => y.ParentID).Contains(x.ID))

In Linq you often work from the bottom up. Here we first get a collection of all the parentIDs in T1 -- the T1.Select(...) part. Then we create a where clause that selects all of the Ts whose IDs are not contained in that set.

Note that the result is a query. To materialize it, use ToList() or similar on the statement above.