Linq to Entities: adding a where condition to a child relationship

Question

For example I have a list of customers which each have a list of orders. Now, I want to get a list of all customers with unpaid orders (let's say this is status 2). Together with that list of customers, I want to have the list of unpaid orders also.

For example I have this:

from c in mycontext.Customers.Include("Orders")
select c

Where or how do I add the condition to look for Orders with status == 2 and how to include these orders with the list of customers?

Answer 1

Try this:

from c in mycontext.Customers.Include("Orders")
from order in c.Orders
where order.status == 2
select order

Or why not simply do this:

from order in mycontext.Orders
where order.status == 2
select order

Answer 2

otherwise

from c in mycontext.Customers.Include("Orders")
where c.Orders.Any(order => order.status == 2)
select c

or

from c in mycontext.Customers.Include("Orders")
let newObject = {
    Customer = c,
    NotPaidOrders = c.Orders.Where(order => order.status == 2).ToList()
}
where newObject.NotPaidOrders.Any()
select newObject