Filtering collections in Entity Framework 4

Question

I have been testing out EF 4 and am looking to filter child collections on an object.

I'm using POCO support and have EF wiring up my collections automatically:

        public virtual ICollection<Product> Products { get; set; }

So in this example, I can get an instance of a category and then enumerate it's products.

What I want to know, is how I can then filter this collection, say to return only active products. I know I can do this in memory but it is important that the criteria is sent direct to the database.

In NHibernate I can do this using filters on my collection, is there something equivalent in EF 4?

I did think about creating another collection e.g.

public virtual ICollection<Product> ActiveProducts {get;set;} 

but am unsure how to then wire this up.

Thanks, Ben

Answer 1

Unfortunately, this isn't officially possible with the current version of Entity Framework. I spent some time diving into this issue myself. I am fairly confident that you could make it work with some reflection-based code where you change the default behavior, but I think it would get somewhat complicated.

I am hardly a DDD expert, but I think the more correct way to do this is to use a specific query to get active Products given an entity of the type of your containing class. You could locate this on either a repository or a query class. If you need to prevent others from accessing this property for efficiency reasons (which is my case), you'll probably want to remove it from the entity class and make your query the only way to access active Products.

If you still want to do db-side collection filtering, my suggestion is to override the delegate field that the proxy classes have for each property. If you grab the sample code from this blog post on viewing generated proxy source and use RedGate's Reflector, you can see the fields I'm talking about. They'll be private static Func<[ProxyType],[PropertyType],[bool]> delegates with names ef_proxy_interceptorFor[PropertyName]. The return value will determine whether the getter returns your base class's property value or the object passed to the delegate (false for the former, true for the latter).

Answer 2

I know a simple workaround, not pretty neat(IMHO)

var idCategory = *get from somewhere*;
var db = new MyContext();
var activeProducts = db.Products.ActiveByCategory(idCategory);

and the extension is something like this

public static class Extensions {
    public static IQueryable<Product> ActiveByCategory(this IQueryable<Product> source, int idCategory) {
        return source.Where(p => p.CategoryId == idCategory && p.Active);
    }
}

poor, but hope it help anyway

EDIT:

another way is

var activeProducts = db.Categories.Where(c => c.Id == idCategory).SelectMany(c => c.Products).Where(p => p.Active);

query generated is

SELECT 
[Extent1].[CategoryId] AS [CategoryId], 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name]
[Extent1].[Active] AS [Active]
FROM [dbo].[Products] AS [Extent1]
WHERE (2 = [Extent1].[CategoryId]) AND ([Extent1].[Active] = 'true')

2 = idCategory