Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 645
Dan Avatar asked Feb 09 '09 11:02

Dan


2 Answers

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

like image 113
Jon Skeet Avatar answered Oct 18 '22 02:10

Jon Skeet


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.

like image 45
Marc Gravell Avatar answered Oct 18 '22 02:10

Marc Gravell