Creating multiple LINQ to SQL blocks

Question

I am trying to split linq into sql queries to make them more readable.

Say I want to return all orders for a product that last 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 would like to do is insert a subquery into 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 allows me to change the original request to:

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

However, this does not work with an exception saying that the method call cannot be translated into SQL when executing the query.

Any quick tips on the right way to achieve this?

Answer 1

How 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 is best to verify that the created sql is the same as the original query.

Answer 2

I'm just taking off my hips, but have you tried changing the return type LastSeasonOrdersto IQueryable<Order>?