Can I load hierarchical data with Entity Framework 4 using ".Include" and filter a Navigation Property based on some criteria

Hello I am using entity framework 4 and I am trying to query Customers and their Orders as navigation property, but I only want to load the orders of a specific date.

When using this:

List<Customer> CustomerResults = ctx.Customers
.Include("Orders") 
.Where(
       c =>
       c.Orders.Any(od =>(od.DateTimeIn >= this.StartDateComboBox.DateTime && 
       od.DateTimeIn <= this.EndDateComboBox.DateTime))
      );
开发者_如何学Python

I get all Orders, if any of the Orders meet the criteria.

Is It possible to filter the navigation property to return only rows that meet specific criteria?

No, there isn't.

In short, eager-loading with Include automatically loads all related records (think a LEFT OUTER JOIN without a filter).

Anytime you want to filter associated records, don't use Include - use an anonymous type projection and EF will just "work out" what needs to be retrieved:

var CustomerResults = ctx.Customers 
                         .Select(x => new
                         {
                            Customer = x,
                            Orders = x.Orders.Where(y => y.DateTimeIn > value)
                         }).ToList();

If you want to return a Customer entity, then just do another projection at the end, just make sure you materialize the query first (.ToList()).

EDIT - To put back into a customer object, example:

var Customers = new List<Customer>();
foreach (var anonType in CustomerResults)
{
   Customer c = anonType.Customer;
   c.Orders = anonType.Orders;
   Customers.Add(c);
}

I'm sure you can do that with a LINQ expression, but i can't remember the syntax.