Using ObjectQuery Include and using a nested where clause

Question

Using entity framework, I'm trying to get back a customer with order details but I want to filter out those Orders that are active.

Customer is our EntityObject which has a collection of Order EntityObjects. CustomerDetails is our ObjectContext.

The code below will attach all orders but I want to filter and only attach those that are active. (Order.active == true). How can I do this?

I know Include builds up a nested query statement (I can observe by using .ToTraceString().) I was hoping to attach a Where clause to this nested select statement or the Include.

            Customer cust;
        CustomerDetails custTable = new CustomerDetails();


        cust = custTable.Customer
            .Where("it.cust_id = " + id)
            .Include("Order")  // But we only want Order.active == true!!!
            .ToList().First();

Answer 1

Untested, but might work?

var temp = custTable.Customer.Where("it.cust_id = " + id).Include("Order");
cust = (from t in temp 
        where t.Order.active == true 
        select t).ToList().First();

Answer 2

I have the same problem, I tried your suggestion; it doesn't filter the include part:

    var miportal = new AdventureWorksEntities();

    // one to many relationship
    var addressTypes = miportal.AddressType
        .Include("CustomerAddress")
        .AsQueryable()
        .Where(at => at.CustomerAddress.Any(ca => ca.CustomerID == 3));

    var result = from addressType in addressTypes
                 where addressType.CustomerAddress.Any(ca => ca.CustomerID == 3)
                 select addressType;

Any solution?

one approach that is what I don't want is to change the direction of your query

cust = custTable.Order .Where(order => order.active == true && order.Customer.Any(customer=>customer.cust_id == id) .Include("Customer")

Then both Customer and Order are filtered.

But I am not clear how to create the original direction as I need that one.