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'd refactor the original like this

private bool IsYourPredicateSatisfied(Maintenance plan)
{
  return plan.CancelDate == null && plan.UpgradeDate == null;
}

Then your Where clause is Where(m => IsYourPredicateSatisfied(m))

Answer 2

Try this:

Expression<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...
         };

I don't have VisualStudio in front of me, so that may require some tweaking. The issue you're running into is that you want to access the IQueryable extension of Where, but just having a Func<T,bool> gives you the IEnumerable extension.