Performing the Equivalent of a SQL not in Query Using LINQ
Sometimes while developing I find the need to simulate a T-SQL not in Query in LINQ. The key is to negate the
enumerable method Contains. It makes sense when you think about it since 'not in' and 'doesn't contain' mean
the same thing.
In the following example lets say we have a list of all dogs represented by the variable allDogs and
we also have a list of all German Shepherd Dogs in another list represented by the variable listOfGermanShepherds.
The following example is a query expression that returns a list of all dogs that are not in the list of German Shepherds:
var queryNotInResults = (from x in allDogs
where !listOfGermanShepherds.Contains(x)
select x).ToList();
The equivalent lambda expression is very much the same and a little more concise:
var lambdaNotIn = dogs.Where(x => !listOfGermanShepherds.Contains(x));
If you are familiar with LINQ this is easy stuff as long as you can remember to use Contains method to do it. Of course
simulating a not in query is not the only reason to use the contains method but it's what I use it for most often.