How to use LINQ to filter property of child collection using (.Any())

Question

How do I do the following SQL in LINQ? I am actually using LINQ to NHibernate (but maybe it's not possible in NHibernate LINQ due to embedded lambda expression I think). But I want to know generally how to do it in LINQ. I’ve never came across this situation before.

SELECT c.CustomerID, c.CustomerName --etc
FROM   Customers c
       INNER JOIN Orders o
         ON c.CustomerID = o.CustomerID
WHERE  o.Status = 1

public class Customer
{
  public int CustomerID { get; set; }
  public string CustomerName { get; set; }  
  public IList<Order> Orders { get; set; }
}

public class Order
{
  public int OrderID { get; set; }
  public int CustomerID { get; set; }
}

I want to do something similar to this:

var customers = 
    (from c in Customers
    where c.Orders.Where(o => o.Status == 1)
    select c).ToList();

THANK YOU!

Answer 1

the where statement returns a collection. you want to use the Any() method

var customers = from c in Customers
                where c.Orders.Any(o => o.Status = 1)
                select c

Answer 2

This solution requires that there is a relationship within LINQ between orders and customer(i.e Customer has an Orders Property) but you can use:

  var customers = 
        (From c in Customers, o In c.Orders
        Where o.Status == 1 
        Select c).ToList()

Cheers!

Answer 3

Are you looking for LAMBDA or straight LINQ to SQL? Are you just looking to do a join?

Here would be a join using a lambda:

var lambda =
Customers.Join (
 Orders,
 c => c.CustomerID,
 o => o.CustomerID,
 (c, o) => new { c, o }
)
.Where (w => w.o.Status == 1)
.Select (s => s.c.CustomerID, s.c.CustomerName);

Answer 4

What would be the linq query that you write to retrieve all Orders from all Customers?

Something to the effect of:

IList<Order> orders = new List<Order>();
foreach (Customer c in Customers)
{
    orders.Union(c.Orders);
}

I tried the following but it throws an "nhibernate collection was not an association" exception:

var orders = from c in Customers
             select c.Orders;

I know I'm missing something, but I can't find a way to achieve this in linq.