Creating reusable chunks of LINQ to SQL

I am trying to break up linq to sql queries to make them a bit more readable.

Say I want to return all orders for product which in the previous year had more than 100 orders. I have this query:

from o in _context.Orders
where (from o1 in _context.Orders 
       where o1.Year == o.Year - 1 && o1.Product == o.Product
       select o1).Count() > 100
select o;

What I'd like to be able to do is to put the nested query in a reusable function:

private IQueryable<Order> LastSeasonOrders(Order order)
{
    return (from o in _context.Orders 
            where o.Year == order.Year - 1 && o.Product == order.Product
            select o);
}

which then lets me change the original query to:

from o in _context.Orders
where LastSeasonOrders(o).Count() > 100
select o;

This doesn't work however with an exceptio开发者_开发技巧n saying that the method call cannot be translated to SQL when the query is run.

Any quick tips on the correct way to achieve this?

What about something like -

void Main()
{
    TypedDataContext _context = ...

    var query = 
        (
            from o in _context.Orders  
            where LastSeasonOrders(_context , o).Count() > 100 
            select o    
        );
     ...
 }      


public static Func<TypedDataContext, Order, IQueryable<Order>> 
     LastSeasonOrders = CompiledQuery.Compile
     (( TypedDataContext _context, Order order) =>

        from o in _context.Orders
        where o.Year == order.Year - 1 && o.Product == order.Product
        select o            
);          

?

It would be best to verify that the sql produced is the same as that produced by your original query.

I am just shooting from the hip but have you tried change return type of LastSeasonOrders to IQueryable<Order>?