Compiling Linq to SQL queries from a non-trivial IQueryable

Question

Is there a way to use the CompiledQuery.Compile method to compile the Expression associated with an IQueryable? Currently I have an IQueryable with a very large Expression tree behind it. The IQueryable was built up using several methods which each supply components. For example, two methods may return IQueryables which are then joined in a third. For this reason I can't explicitly define the entire expression within the compile() method call.

I was hoping to pass the expression in to the compile method as someIQueryable.Expression, however this expression isn't in the form required by the compile method. If I try and get around this by putting the query directly into the compile method, eg:

    var foo = CompiledQuery.Compile<DataContext, IQueryable<User>>(dc => dc.getUsers());
    var bar = foo(this);

where I make the call form within a datacontext, I get an error saying that "getUsers is not mapped as a stored procedure or user-defined function". Again I can't just copy the contents of the getUsers method to where I make the compile call since it in turn makes use of other methods.

Is there some way to pass the Expression on the IQueryable returned from "getUsers" into the Compile method?

Updated I tried to force my will on the system with the following code:

    var phony = Expression.Lambda<Func<DataContext, IQueryable<User>>>(
        getUsers().Expression, Expression.Parameter(typeof(DataContext), "dc"));

    Func<DataContext, IQueryable<User>> wishful = CompiledQuery.Compile<DataContext, IQueryable<User>>(phony);
    var foo = wishful(this);

foo ends up being:

{System.Data.Linq.SqlClient.SqlProvider+OneTimeEnumerable`1[Model.Entities.User]}

I don't have the option to see the results in foo, as instead of offering to expand the results and run the query I only see the message "Operation could destabilize the runtime".

I just need to find a way for the sql string to only be generated once and used as a paramaterized command on subsequent requests, I can do this manually using the GetCommand method on the data context, but then I have to explicitly set all the parameters and do the object mapping myself, which is a few hundred lines of code given the complexity of this particular query.

Updated

John Rusk provided the most useful information, so I awarded him the win on this one. However, some extra tweaking was required and there were a couple of other issues I encountered along the way so I thought I'd 'Expand' on the answer. Firstly, the 'Operation could destabalize the runtime' error was not due to the compilation of the expression, it was actually because of some casting deep in the expression tree. In some places I needed to call the .Cast<T>() method to formally cast items, even when they were of the correct type. Without going into too much detail this was basically required when several expression were combined into a single tree and each branch could return a different type, which were each the sub type of a common class.

After solving the destabalizing issue, I returned to the compilation problem. John's expand solution was almost there. It looked for method call expressions in the tree and tried to resolve them to the underlying expression that method would usually return. My references to expressions were not provided by method calls, but instead properties. So I needed to modify the expression visitor that performs the expansion to include these types:

protected override Expression VisitMemberAccess(MemberExpression m) {
    if(m.Method.DeclaringType == typeof(ExpressionExtensions)) {
        return new ExpressionExpander().Visit((Expression)(((System.Reflection.PropertyInfo)m.Member).GetValue(null, null)));
    }
    return base.VisitMemberAccess(m);
}

This method may not be appropriate in all cases, but it should help anyone who finds themselves in the same predicament.

Answer 1

Did you come accross this http://blogs.msdn.com/mitsu/archive/2008/10/27/linq-to-sql-compiledquery-container.aspx

Answer 2

Something like this works, at least in my tests:

Expression<Func<DataContext, IQueryable<User>> queryableExpression = GetUsers();
var expressionWithSomeAddedStuff = (DataContext dc) => from u in queryableExpression.Invoke(dc) where ....;
var expressionThatCanBeCompiled = expressionWithSomeAddedStuff.Expand();
var foo = CompiledQuery.Compile<DataContext, IQueryable<User>>(expressionThatCanBeCompiled);

This looks a bit verbose, and there are probably improvements that you can make.

They key point is that it uses the Invoke and Expand methods from LinqKit. They basically let you build up a query, through composition, and then compile the finished result.