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