Converting IQueryable to IQueryable<T> with Dynamic LINQ

In a previous post I talked about using Dynamic LINQ. While extremely powerful, the results of all Dynamic LINQ queries are IQueryable, which loses the benefits of working with a strongly typed IQueryable<T>. Even worse, the elements in an IQueryable result of a Dynamic LINQ query are generated on the fly and derived from DynamicClass included in the sample, so you can't even build a static converter to get query results into your own type. I struggled with this off and on for weeks, because I needed a way to return an IQueryable<T> from a function that used Dynamic LINQ without enumerating the results.

I finally found the solution in a wonderful enhancement of Microsoft's Dynamic LINQ sample. Krzysztof Olczyk has worked some magic to add several handy features to Dynamic.cs, one of which allows the casting of query results back to a known type. So with his version of Dynamic.cs, you can do this:

IQueryable<MyType> Results = MyResults.Select<MyType>("new @out (Prop1, Prop2)");

This is great because it bridges the gap between the performance and flexibility of Dynamic LINQ and the type-safety of standard LINQ, and you get the best of both worlds!

UPDATE: The relevant code for the Select<T> function is below, but I highly recommend you check out the other enhancements he as added as well.

        private static IQueryable Select(Type type, IQueryable source, string selector, object[] values) {
            if (source == null) throw new ArgumentNullException("source");
            if (selector == null) throw new ArgumentNullException("selector");
            LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, type, selector, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Select",
                    new Type[] { source.ElementType, lambda.Body.Type },
                    source.Expression, Expression.Quote(lambda)));
        }
                
                public static IQueryable Select(this IQueryable source, string selector, params object[] values) {
                        return Select(null, source, selector, values);
                }
                
                public static IQueryable<T> Select<T>(this IQueryable source, string selector, params object[] values) {
                        return Select(typeof(T), source, selector, values) as IQueryable<T>;
                }

Seek predicates on multi-column indexes

While trying to improve performance of some of my SQL server queries, I ran across an excellent article by Craig Freedman that explains how SQL server creates seek predicates when using an index seek to find rows in a table.

The key point I took away from this (and that I didn't know before) is that a multi-column index won't utilize the second column unless the first is searched as an equality. In my current project, we have two dates to represent when a row was added to the table and when it is removed. This is a great system for keeping unlimited change tracking and history data while keeping everything in one table, but it makes searches slow because only the first date is used as an index seek predicate. Most of our queries are something like this:

select FAID from ESP_FAs where Admit <= '3/28/2011 12:00:00' AND Retire > '3/28/2011 12:00:00'

This will get all the rows that exist on the specified date and haven't been deleted. The problem is that based on Craig's article above, only the first date is used in the seek predicate if Admit and Retire are both columns in an index. The retire clause becomes a residual predicate, so having it in the multi-column index isn't really helping us much.

So far the best solution I've found for this is to make sure that the first column included in a multi-column index that is not being compared to with equality is the most limiting. This reduces the number of rows SQL server has to sort through with the residual predicate.