Filtered association

Is it possible to access members of association selectively, using some kind of filter?

To illustrate what I'm trying to achieve, I have a Products table with a one-to-many association against a ProductPrices table. The prices are based on a start date, and in most instances I'm only interested in a single one - the effective price.

I have tried implementing a method that looks like this:

public ProductPrice EffectivePrice(DateTime effectiveDate)
{
    get
    {
        return this.ProductPrices.Where(x => x.EffectiveDate <= effectiveDate)
                    .OrderByDescending(x => x.EffectiveDate)
                    .FirstOrDefault();
    }
}

However, when I look at the SQL that is being run here, the association loads in full, and the Where clause applies to the in-memory ProductPrice collection. The problem is there are several of these child tables, and several processes that operate on the whole Products collection, sending memory use through the roof.

I can think of a workaround where I effectively select the appropriate child entities separately and then "inject" them into my Product object but it's a bit clunky. Is there a way of treating collections as IQueryable rather than IEnumerable?

No this isn't currently possible. The EntityCollections are loaded as a full set, so with your LINQ query because this operates over an in-memory set the collection gets lazily loaded first (which leads to the SQL statement you see issued) and then the LINQ query operates over that set. Your best bet would be to either store the aggregate value on the object itself, or rather than using LINQ issue the query via the querying API instead.

e.g. Something like this should give you an equivalent query, but this will be run as a server side query and does not touch the association.

var query = new Query(typeof(ProductPrice), Entity.Attribute("ProductId") == this.Id && Entity.Attribute("EffectiveDate") <= effectiveDate) { Order = Order.By("EffectiveDate") };
return this.UnitOfWork.Find(query).FirstOrDefault();

Jeremy