Seemingly simple LINQ query is throwing me off. I want all items in array A unless it exists in array B

Question

So I have this query. Both currentSurveyors and surveyorsInState are arrays of the same type. currentSurveyors could have 0 or more items. I want to return all the surveyorsInState except if that entry is in currentSurveyors. What am I doing wrong?

        from current in currentSurveyors
        from surveyors in surveyorsInState
        where (surveyors.SurveyorID != current.SurveyorID || 
        currentSurveyors.Count() == 0)
        select surveyors;

Answer 1

surveyorsInState.Except(currentSurveyors)

Answer 2

Paul's solution is the right one - it should be:

surveyorsInState.Except(currentSurveyors)

However, it's worth looking at why your original query fails, and what it's actually doing. Here it is again:

from current in currentSurveyors
from surveyors in surveyorsInState
where (surveyors.SurveyorID != current.SurveyorID || 
       currentSurveyors.Count() == 0)
select surveyors;

Think about the values of surveyors and current before the where. You're basically doing a cross-join - so if currentSurveyors is { A, B, C } and surveyorsInState is { B, C, D } then before the where you'll get all of these:

current    surveyors
   A            B
   A            C
   A            D
   B            B
   B            C
   B            D
   C            B
   C            C
   C            D

Now for your where clause - currentSurveyors.Count() will never be zero - you're just checking whether the original sequence is empty or not. The fact that you've got to the where clause at all suggests that's not the case! So all you're doing is throwing away rows where the two IDs match, leaving:

current    surveyors
   A            B
   A            C
   A            D
   B            C
   B            D
   C            B
   C            D

You're then selecting just surveyors, so we get rid of the left hand column:

surveyors
     B
     C
     D
     C
     D
     B
     D

That's the result you'll have been seeing before.

Do you understand why now? It's a good idea to imagine the sequence of results from each line in the query (here I elided the first two lines, but you don't have to) - that way you can trace what's going on.

Answer 3

I believe it's

from surveyors in surveyorsInState 
where !(from current in currentSurveyors select current.SurveyorID).Contains(surveyors.SurveyorID) 
select surveyors;