Skip Navigation Links
Electric Harbour
Danish Alt Codes
Finnish Alt Codes
French Alt Codes
Norwegian Alt Codes
Spanish Alt Codes
Scandanavian Alt Codes
Swedish Alt Codes
Bulk Identity Insert
SQL Server Constraints
Visual Studio Shortcuts
LINQ Cast Operator
LINQ Group By Example
LINQ Not In Example
LINQ Order By Example
CSV String Generator
Curriculum Vitæ

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.