EF Filtering a Child Table with Lazy Load

Question

I'm using entity framework with POCOs and the repository pattern and am wondering if there is any way to filter a child list lazy load. Example:

class Person
{
    public virtual Organisation organisation {set; get;}
}
class Organisation
{
    public virtual ICollection<Product> products {set; get;}
}
class Product
{
    public bool active {set; get;}
}

Currently I only have a person repository because I'm always starting from that point, so ideally I would like to do the following:

Person person = personRepo.GetById(Id);
var products = person.organisation.products;

And have it only load products where active = true from the database.

Is this possible and if so how?

EDIT My best guess would be either a filter can be added to the configuration of the entity. Or there might be a way to intercept/override the lazy load call and modify it. Obviously if I created an Organisation Repository I could manually load it as I please but I am trying to avoid that.

Answer 1

There's not a direct way to do this via lazy loading, but if you were willing to explicitly load the collection, you could follow whats in this blog, see the Applying filters when explicitly loading related entities section.

context.Entry(person)
    .Collection(p => p.organisation.products)
    .Query()
    .Where(u => u.IsActive)
    .Load();

Answer 2

This might be related:

Using CreateSourceQuery in CTP4 Code First

If you were to redefine your properties as ICollection<T> rather than IList<T> and enable change-tracking proxies, then you might be able to cast them to EntityCollection<T> and then call CreateSourceQuery() which would allow you to execute LINQ to Entities queries against them.

Example:

var productsCollection = (EntityCollection<Product>)person.organisation.products;
var productsQuery = productsCollection.CreateSourceQuery();
var activeProducts = products.Where(p => p.Active);

Answer 3

You can do what Mark Oreta and luksan suggest while keeping all the query logic within the repository.

All you have to do is pass a Lazy<ICollection<Product>> into the organization constructor, and use the logic they provided. It will not evaluate until you access the value property of the lazy instance.

UPDATE

/*  
    First, here are your changes to the Organisation class: 
    Add a constructor dependency on the delegate to load the products to your 
    organization class.  You will create this object in the repository method 
    and assign it to the Person.Organization property 
*/
public class Organisation
{
    private readonly Lazy<ICollection<Product>> lazyProducts;
    public Organisation(Func<ICollection<Product>> loadProducts){
        this.lazyProducts = new Lazy<ICollection<Product>>(loadProducts);
    }

    //  The underlying lazy field will not invoke the load delegate until this property is accessed
    public virtual ICollection<Product> Products { get { return this.lazyProducts.Value; } }
}

Now, in your repository method, when you construct the Person object you will assign the Organisation property with an Organisation object containing the lazy loading field.

So, without seeing your whole model, it will looks something like

 public Person GetById(int id){
    var person = context.People.Single(p => p.Id == id);

    /*  Now, I'm not sure about the cardinality of the person-organization or organisation
        product relationships, but let's assume you have some way to access the PK of the 
        organization record from the Person and that the Product has a reference to 
        its Organisation.  I may be misinterpreting your model, but hopefully you 
        will get the idea
     */

     var organisationId = /*  insert the aforementioned magic here */

     Func<ICollection<Product>> loadProducts = () => context.Products.Where(product => product.IsActive && product.OrganisationId == organisationId).ToList(); 

    person.Organisation = new Organisation( loadProducts );

    return person;
 }

By using this approach, the query for the products will not be loaded until you access the Products property on the Organisationinstance, and you can keep all your logic in the repository. There's a good chance that I made incorrect assumptions about your model (as the sample code is quite incomplete), but I think there is enough here for you to see how to use the pattern. Let me know if any of this is unclear.

Answer 4

You could possibly use Query() method to achieve this. Something like:

 context.Entry(person)
            .Collection(p => p.organisation.products)
            .Query()
            .Where(pro=> pro.Active==true)
            .Load();

Have a look at this page click here

Answer 5

Is your repository using something like:

IQueryable<T> Find(System.Linq.Expressions.Expression<Func<T, bool>> expression)

If so you can do something like this:

var person = personRepo.Find(p => p.organisation.products.Any(e => e.active)).FirstOrDefault();