The NOT IN clause in LINQ to SQL

The NOT IN clause in LINQ to SQL

 

Programmers used to write SQL queries often want to know a LINQ syntax equivalent to the SELECT NOT IN clause in SQL.

Consider this code that returns all the customers who don't have an order in the Orders table. This is one SQL query that returns that value.

 

SELECT *
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] NOT IN (
    SELECT [t1].[CustomerID]
    FROM [dbo].[Orders] AS [t1]
)

 

This is not the faster way to get the desired result (using a NOT EXISTS is the favorite way - more on this shortly). LINQ offers a Contains extension method that allows writing the following code.

 

NorthwindDataContext dc = new NorthwindDataContext();

dc.Log = Console.Out;

var query =

    from c in dc.Customers

    where !(from o in dc.Orders

            select o.CustomerID)

           .Contains(c.CustomerID)

    select c;

foreach (var c in query) Console.WriteLine( c );

 

In LINQ to SQL the query is translated into this SQL code:

 

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],

       [t0].[ContactTitle], [t0].[Address], [t0].[City],

       [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE NOT (EXISTS(

    SELECT NULL AS [EMPTY]

    FROM [dbo].[Orders] AS [t1]

    WHERE [t1].[CustomerID] = [t0].[CustomerID]

    ))

 

This approach is not only semantically equivalent, but also faster in execution. The following is the result with SET STATISTICS IO ON. The first result is for the hand-written query that use the NOT IN clause.  The second result is for the LINQ to SQL generated query.

 

(2 row(s) affected)

Table 'Orders'. Scan count 182, logical reads 364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customers'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(2 row(s) affected)

Table 'Orders'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Customers'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

The LINQ query is not always similar to SQL (like from/select), but in general you should be able to write in LINQ a SQL query. However, there is not always a direct translation for each single SQL keyword, but you can get the same result with semantically equivalent statements.

 

 

Thanks & Regards,

Arun Manglick || Senior Tech Lead