The T-SQL in can be expressed in Linq in a couple of ways, if we have sql that looks like:
Select ProductID from Products where SupplierID not in (Select SupplierID from Suppliers where City like 'b%')"
(1) we can take advantage of deferred execution and break the query into logical parts which is
much more readible:
var subquery = from supplier in db.Suppliers
where !supplier.City.StartsWith("B")
select supplier.SupplierID;
List
prods = (from product in db.Products
where !subquery.Contains((int)product.SupplierID)
select product).ToList();
The first query is not executed till the ToList so the resultant SQL looks like:
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]FROM [dbo].[Products] AS [t0]WHERE NOT (EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[Suppliers] AS [t1] WHERE ([t1].[SupplierID] = ([t0].[SupplierID])) AND (NOT ([t1].[City] LIKE @p0)) ))
(2) just inline the subquery
List prods = (from product in db.Products
where !(from supplier in db.Suppliers
where !supplier.City.StartsWith("B")
select supplier.SupplierID).Contains((int)product.SupplierID)
select product).ToList();
this reults in the same T-SQL
