EntitySet<T>.Where(myPredicate) throws NotSupportedException

Question

EDIT: Let's try this again. This time I've used the AdventureWorks sample database so you can all play along. This will rule out anything crazy I've done in my own database. Here's a new example demonstrating what works and what I would expect to work (but doesn't). Can anyone explain why it doesn't work or suggest a different way of achieving my goal (refactoring out the common expression so it can be reused elsewhere)?

using (AdventureWorksDataContext db = new AdventureWorksDataContext())
{
    // For simplicity's sake we'll just grab the first result.
    // The result should have the name of the SubCategory and an array of Products with ListPrice greater than zero.
    var result = db.ProductSubcategories.Select(subCategory => new
    {
        Name = subCategory.Name,
        ProductArray = subCategory.Products.Where(product => product.ListPrice > 0).ToArray()
    }).First();
    Console.WriteLine("There are {0} products in SubCategory {1} with ListPrice > 0.", result.ProductArray.Length, result.Name);
    // Output should say: There are 3 products in SubCategory Bib-Shorts with ListPrice > 0.

    // This won't work.  I want to pull the expression out so that I can reuse it in several other places.
    Expression<Func<Product, bool>> expression = product => product.ListPrice > 0;
    result = db.ProductSubcategories.Select(subCategory => new
    {
        Name = subCategory.Name,
        ProductArray = subCategory.Products.Where(expression).ToArray() // This won't compile because Products is an EntitySet<Product> and that doesn't have an overload of Where that accepts an Expression.
    }).First();
    Console.WriteLine("There are {0} products in SubCategory {1} with ListPrice > 0.", result.ProductArray.Length, result.Name);
}

</Edit>

The following LINQ to SQL works fine:

var result = from subAccount in db.SubAccounts
             select new ServiceTicket
             {
                 MaintenancePlans = subAccount.Maintenances.Where(plan => plan.CancelDate == null && plan.UpgradeDate == null).Select(plan => plan.ToString()).ToArray()
                 // Set other properties...
             };

However, I want to break out the predicate passed to the Where since it's used throughout the code. But if I try and pass a defined predicate into the Where it fails, such as:

Func<DatabaseAccess.Maintenance, bool> activePlanPredicate = plan => plan.CancelDate == null && plan.UpgradeDate == null;
var result = from subAccount in db.SubAccounts
             select new ServiceTicket
             {
                 MaintenancePlans = subAccount.Maintenances.Where(activePlanPredicate).Select(plan => plan.ToString()).ToArray()
                 // Set other properties...
             };

This makes no sense to me. Can anyone explain what's going on? Maintenances is of type EntitySet<DatabaseAccess.Maintenance>. The error I get is:

System.NotSupportedException: Unsupported overload used for query operator 'Where'..

EDIT: For those interested, here's what Reflector has for the first (working) example with Optimization set to .NET 2.0:

using (BugsDatabaseDataContext db = new BugsDatabaseDataContext())
{
    ParameterExpression CS$0$0001;
    ParameterExpression CS$0$0006;
    ParameterExpression CS$0$0010;
    return db.SubAccounts.Select<SubAccount, ServiceTicket>(Expression.Lambda<Func<SubAccount, ServiceTicket>>(
        Expression.MemberInit(
            Expression.New(
                (ConstructorInfo) methodof(ServiceTicket..ctor), 
                new Expression[0]), 
                new MemberBinding[] 
                { 
                    Expression.Bind(
                        (MethodInfo) methodof(ServiceTicket.set_MaintenancePlans), 
                        Expression.Call(
                            null, 
                            (MethodInfo) methodof(Enumerable.ToArray), 
                            new Expression[] 
                            { 
                                Expression.Call(
                                    null, 
                                    (MethodInfo) methodof(Enumerable.Select), 
                                    new Expression[] 
                                    { 
                                        Expression.Call(
                                            null, 
                                            (MethodInfo) methodof(Enumerable.Where), 
                                            new Expression[] 
                                            { 
                                                Expression.Property(CS$0$0001 = Expression.Parameter(typeof(SubAccount), "subAccount"), (MethodInfo) methodof(SubAccount.get_Maintenances)), 
                                                Expression.Lambda<Func<Maintenance, bool>>(
                                                    Expression.AndAlso(
                                                        Expression.Equal(
                                                            Expression.Property(CS$0$0006 = Expression.Parameter(typeof(Maintenance), "plan"), (MethodInfo) methodof(Maintenance.get_CancelDate)), 
                                                            Expression.Convert(Expression.Constant(null, typeof(DateTime?)), typeof(DateTime?)), false, (MethodInfo) methodof(DateTime.op_Equality)
                                                        ), 
                                                        Expression.Equal(
                                                            Expression.Property(CS$0$0006, (MethodInfo) methodof(Maintenance.get_UpgradeDate)), 
                                                            Expression.Convert(Expression.Constant(null, typeof(DateTime?)), typeof(DateTime?)), false, (MethodInfo) methodof(DateTime.op_Equality)
                                                        )
                                                    ), 
                                                    new ParameterExpression[] { CS$0$0006 }
                                                ) 
                                            }
                                        ), 
                                        Expression.Lambda<Func<Maintenance, string>>(
                                            Expression.Call(
                                                CS$0$0010 = Expression.Parameter(typeof(Maintenance), "plan"), 
                                                (MethodInfo) methodof(object.ToString), 
                                                new Expression[0]
                                            ), 
                                            new ParameterExpression[] { CS$0$0010 }
                                        ) 
                                    }
                                ) 
                            }
                        )
                    )
                }
            ), 
            new ParameterExpression[] { CS$0$0001 }
        )
    ).ToList<ServiceTicket>();
}

EDIT: The Reflector output for the second example (using a predicate) is mostly similar. The biggest difference being that, in the call to Enumerable.Where, rather than passing an Expression.Lambda it passes Expression.Constant(activePlanPredicate).

Answer 1

I don't fully understand the guts of Linq to Entities, but there is an Open Source (usable in proprietary software) toolkit specifically designed to help solve this problem, called LinqKit, linked off this O'Reilly-related article:

http://www.albahari.com/nutshell/predicatebuilder.aspx

Since I don't fully understand the guts, I'll just quote them:

Entity Framework's query processing pipeline cannot handle invocation expressions, which is why you need to call AsExpandable on the first object in the query. By calling AsExpandable, you activate LINQKit's expression visitor class which substitutes invocation expressions with simpler constructs that Entity Framework can understand.

Here is a direct link to LinqKit.

And here is the type of code that this project enables:

using LinqKit;

// ...

Expression<Func<Product, bool>> expression = product => product.ListPrice > 0;

var result = db.ProductSubcategories
    .AsExpandable() // This is the magic that makes it all work
    .Select(
        subCategory => new
        {
            Name = subCategory.Name,
            ProductArray = subCategory.Products
                // Products isn't IQueryable, so we must call expression.Compile
                .Where(expression.Compile())
        })
    .First();

Console.WriteLine("There are {0} products in SubCategory {1} with ListPrice > 0."
    , result.ProductArray.Count()
    , result.Name
    );

The result is:

There are 3 products in SubCategory Bib-Shorts with ListPrice > 0.

Yay, no exception, and we can extract the predicate!