Linq-to-Entities ~ When using Include to add a child table, how can I filter on child table properties?

Question

Is there a way to apply Where clauses on to the Included child tables?

Example: I have a Customers entity set and and Addresses entity set, and I've appropriately decorated the metadata class with the [Include] attribute. I can easily filter on a property of Customer, such as last name...

public IQueryable<Alphagram> GetCustomersWithAddresses()
{
    return this.ObjectContext.Customers.Include("Address")
         .Where(w => w.LastName == "Smith")
}

But say I wanted to also filter on a property of he Addresses child table, such Address.City? Addresses exists as a property of Customers, but intellisense doesn't pick up any of the columns of Addresses.

Am I better off using joins instead of Include?

Answer 1

You could do something like this:

public IQueryable<Alphagram> GetCustomersWithAddresses()
{
    return this.ObjectContext.Customers.Include("Address")
         .Where(w => w.LastName == "Smith" && w.Address.Any(a => a.City == "Some city") /* w.Address.All(...) */)
}

Now it depends on what you wish to accomplish and how customers and addresses are related (1-1, 1-* etc).

Regards...