Linq to Entities - Custom method (OrderBy) fails in lambda statement

Question

I am trying to write a custom OrderBy extension method for Linq to Entities, where I can sort ASC or DESC based on a parameter. My first attempt was the following:

public static class LinqExtensions
{   
    public static IOrderedQueryable<TSource> OrderByExtension<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector, bool isDescending = true)
    {
        return (isDescending) ? source.OrderByDescending(keySelector) : source.OrderBy(keySelector);
    }
}

This works great!... As long as I don't use this method inside a lambda statement. A toy example would be:

using (var dbContext = new FooDBEntities())
{
    var foo = dbContext.Fubars.SelectMany(x => dbContext.Fubars.OrderByExtension(y => y.Foo, true)).ToList();
}

When I run the above code the program blows out and I get the following error:

Unhandled Exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Linq.IOrderedQueryable`1[ExpressionTreeFoo.Fubar] OrderByExtension[Fubar,String](System.Linq.IQueryable`1[ExpressionTreeFoo.Fubar], System.Linq.Expressions.Expression`1[System.Func`2[ExpressionTreeFoo.Fubar,System.String]], Boolean)' method, and this method cannot be translated into a store expression.

The error pretty much says it all. The framework does not know how to translate my custom extension method into something that SQL can understand.

I've been doing some googling but I haven't seen anything that says definitively that this is or is not possible. So my question is.. Is there any way at all to make a custom OrderBy method (extension method or not) that will work inside a lambda expression?

Answer 1

I finally found a solution by using the NuGet Package: LinqKit.EntityFramework

The way it works is you first call .AsExpandable() this puts a wrapper around the IQueryable. Then you have to call .Invoke on the inner lambda expression. Invoke is an extension method of LinqKit. When this query is called, LinqKit will recursively scan threw the expression tree and when it hits an expression that calls the extension Invoke it will remove the call to Invoke and re-write the expression tree in a way that the CLR engine can understand. A full description can be found at: http://www.albahari.com/nutshell/linqkit.aspx

static void EFTest()
{
    using (var dbContext = new DBEntities())
    {
        // debug - print query
        dbContext.Database.Log = Console.WriteLine;

        // get order by expression
        var orderByExpression = GetOrderByExpression(dbContext, true);

        // execute query
        var result = dbContext.FooTables
                              .AsExpandable()
                              .SelectMany(x => orderByExpression.Invoke(x))
                              .ToList();
    }
}

private static Expression<Func<FooTable, IOrderedQueryable<FooTable>>> GetOrderByExpression(DBEntities dbContext, bool isDesending)
{
    if (isDesending)
    {
        return x => dbContext.FooTables.OrderByDescending(y => y.Date);
    }
    else
    {
        return x => dbContext.FooTables.OrderBy(y => y.Date);
    }
}