Linq Sub-Select

How do I write a sub-select in LINQ.

If I have a list of customers and a list of orders I want all the customers that have no orders.

This is my pseudo code attempt:

    var  res = from c in customers 
where c.CustomerID ! in (from o in orders select o.CustomerID) 
select c

From stackoverflow

• If this is database-backed, try using navigation properties (if you have them defined):

  var res = from c in customers
            where !c.Orders.Any()
            select c;
  

  On Northwind, this generates the TSQL:

  SELECT /* columns snipped */
  FROM [dbo].[Customers] AS [t0]
  WHERE NOT (EXISTS(
      SELECT NULL AS [EMPTY]
      FROM [dbo].[Orders] AS [t1]
      WHERE [t1].[CustomerID] = [t0].[CustomerID]
      ))
  

  Which does the job quite well.

• var  res = (from c in orders where c.CustomerID == null
                 select c.Customers).ToList();
  

  or Make Except()

• How about:

  var res = from c in customers 
            where !orders.Select(o => o.CustomerID).Contains(c.CustomerID)
            select c;
  

  Another option is to use:

  var res = from c in customers
            join o in orders 
                 on c.CustomerID equals o.customerID 
                 into customerOrders
            where customerOrders.Count() == 0
            select c;
  

  Are you using LINQ to SQL or something else, btw? Different flavours may have different "best" ways of doing it

  Ray Booysen : Isn't using Any() instead of Count() slightly better in terms of readability? Was reading Bill Wagner's More Effective C# and this was one of the recommendations.

  Jon Skeet : Yes, quite possibly. Lots of ways of doing it. Arguably it would be nice to have an Empty() or None() extension method which is the opposite of Any() too...

  Ray Booysen : Yeah, good idea.