How to maintain LINQ deferred execution?

Question

Suppose I have an IQueryable<T> expression that I'd like to encapsulate the definition of, store it and reuse it or embed it in a larger query later. For example:

IQueryable<Foo> myQuery =
 from foo in blah.Foos
 where foo.Bar == bar
 select foo;

Now I believe that I can just keep that myQuery object around and use it like I described. But some things I'm not sure about:

1. How best to parameterize it? Initially I've defined this in a method and then returned the IQueryable<T> as the result of the method. This way I can define blah and bar as method arguments and I guess it just creates a new IQueryable<T> each time. Is this the best way to encapsulate the logic of an IQueryable<T>? Are there other ways?

2. What if my query resolves to a scalar rather than IQueryable? For instance, what if I want this query to be exactly as shown but append .Any() to just let me know if there were any results that matched? If I add the (...).Any() then the result is bool and immediately executed, right? Is there a way to utilize these Queryable operators (Any, SindleOrDefault, etc.) without immediately executing? How does LINQ-to-SQL handle this?

Edit: Part 2 is really more about trying to understand what are the limitation differences between IQueryable<T>.Where(Expression<Func<T, bool>>) vs. IQueryable<T>.Any(Expression<Func<T, bool>>). It seems as though the latter isn't as flexible when creating larger queries where the execution is to be delayed. The Where() can be appended and then other constructs can be later appended and then finally executed. Since the Any() returns a scalar value it sounds like it will immediately execute before the rest of the query can be built.

Answer 1

1. You have to be really careful about passing around IQueryables when you're using a DataContext, because once the context get's disposed you won't be able to execute on that IQueryable anymore. If you're not using a context then you might be ok, but be aware of that.

2. .Any() and .FirstOrDefault() are not deferred. When you call them they will cause execution to occur. However, this may not do what you think it does. For instance, in LINQ to SQL if you perform an .Any() on an IQueryable it acts as a IF EXISTS( SQL HERE ) basically.

You can chain IQueryable's along like this if you want to:

var firstQuery = from f in context.Foos
                    where f.Bar == bar
                    select f;

var secondQuery = from f in firstQuery
                    where f.Bar == anotherBar
                    orderby f.SomeDate
                    select f;

if (secondQuery.Any())  //immediately executes IF EXISTS( second query in SQL )
{
    //causes execution on second query 
    //and allows you to enumerate through the results
    foreach (var foo in secondQuery)  
    {
        //do something
    }

    //or

    //immediately executes second query in SQL with a TOP 1 
    //or something like that
    var foo = secondQuery.FirstOrDefault(); 
}

Answer 2

A much better option than caching IQueryable objects is to cache Expression trees. All IQueryable objects have a property called Expression (I believe), which represents the current expression tree for that query.

At a later point in time, you can recreate the query by calling queryable.Provider.CreateQuery(expression), or directly on whatever the provider is (in your case a Linq2Sql Data Context).

Parameterizing these expression trees is slightly harder however, as they use ConstantExpressions to build in a value. In order to parameterize these queries, you WILL have to rebuild the query every time you want different parameters.

Answer 3

Any() used this way is deferred.

var q = dc.Customers.Where(c => c.Orders.Any());

Any() used this way is not deferred, but is still translated to SQL (the whole customers table is not loaded into memory).

bool result = dc.Customers.Any();

If you want a deferred Any(), do it this way:

public static class QueryableExtensions
{
  public static Func<bool> DeferredAny<T>(this IQueryable<T> source)
  {
    return () => source.Any();
  }
}

Which is called like this:

Func<bool> f = dc.Customers.DeferredAny();
bool result = f();

The downside is that this technique won't allow for sub-querying.

Answer 4

Create a partial application of your query inside an expression

Func[Bar,IQueryable[Blah],IQueryable[Foo]] queryMaker = 
(criteria, queryable) => from foo in queryable.Foos
        where foo.Bar == criteria
        select foo;

and then you can use it by ...

IQueryable[Blah] blah = context.Blah;
Bar someCriteria = new Bar();
IQueryable[Foo] someFoosQuery = queryMaker(blah, someCriteria);

The query could be encapsulated within a class if you want to make it more portable / reusable.

public class FooBarQuery
{
  public Bar Criteria { get; set; }

  public IQueryable[Foo] GetQuery( IQueryable[Blah] queryable )
  {
     return from foo in queryable.Foos
        where foo.Bar == Criteria
        select foo;
  } 
}