EF IQueryable extension method not working in Sele

Question

Possible Duplicate:
LINQ to Entities does not recognize the method

I use Entity Framework 4.3

I write extension method:

public static IQueryable<TSource> Active<TSource>(this IQueryable<TSource> source) where TSource : class, IStatusable
{
    return source.Where(s => s.Status == (int)StatusEnum.Enabled);
}

This works good:

var cat=Context.Categories.Active().ToList()

But i need use this extension method in Select. Look simplified query:

return Context.Categories
 .Select(c => new { Children=c.Children.AsQueryable().Active()})
 .ToList()

(Children - collection of child categories) When query execution I get a error message:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Portal.FrontOffice.Model.Category] Active[Category](System.Linq.IQueryable`1[Portal.FrontOffice.Model.Category])' method, and this method cannot be translated into a store expression.

Why does not work? How to write correctly?

Answer 1

As stated in my comments, it is the very same reason every time this error message appears:

The expression tree that is used by the EF provider to create the SQL contains a method it doesn't understand.
In your case, this is the Active extension method. It is part of the expression tree as it is used inside another expression (Select).

In your first query, your method is NOT part of the expression tree. Instead it simply changes the expression tree by adding the Where expression to it. That is a fundamental difference.

To make your second query work, use this:

return Context.Categories 
              .Select(c => new { Children=c.Children
                                           .Where(s => s.Status == 
                                                       (int)StatusEnum.Enabled) }) 
              .ToList()