How to pass an Expression into Entity Framework LINQ query OrderBy clause

Question

I have the following LINQ query:

using (var context = new EUContext())
        {
            var tmp = context.Terms.Include(x => x.StudentCourses)
                .Where(x => x.StudentID == studentId && x.DepartmentID == departmentId)
                .OrderBy(x => x.AcademicYear)
                .ThenBy(x=> x.TermRegistered == "Fall" ? 1 :
                            x.TermRegistered == "Spring" ? 2 : 3));

            return tmp.ToList();
        }

I am trying to move the OrdyBy in the ThenBy clause to clean up the code. I am trying to use an expression as following:

private static Expression<Func<string, int>> TermsOrder(string x)
        {
            return (x == "Fall" ? 1 :
                    x == "Spring" ? 2 : 3);
        }

and my code should look like this:

using (var context = new EUContext())
            {
                var tmp = context.Terms.Include(x => x.StudentCourses)
                    .Where(x => x.StudentID == studentId && x.DepartmentID == departmentId)
                    .OrderBy(x => x.AcademicYear)
                    .ThenBy(x=> TermsOrder(x.TermRegistered));

                return tmp.ToList();
            }

Unfortunately the expression doesn't work there is a long squiggly line in the body of the expression with the following error message:

Cannot implicitly convert type 'int' to 'System.Linq.Expressions.Expression>

What am I doing wrong? This is my first try on using expressions and I know that I am missing something obvious due to not fully understanding how expressions work.

Thanks

Answer 1

This isn't as simple as it seems. You need to combine Expressions or build Expressions to generate what you want, and unfortunately C# doesn't include a lot of help in that area.

The easiest approach is to use an extension method for LambdaExpression composition. It depends on some Expression extension methods for replacing one Expression with another in an Expression:

public static class ExpressionExt {
    // Compose: f.Compose(g) => x => f(g(x))
    /// <summary>
    /// Composes two LambdaExpression into a new LambdaExpression: f.Compose(g) => x => f(g(x))
    /// </summary>
    /// <param name="fFn">The outer LambdaExpression.</param>
    /// <param name="gFn">The inner LambdaExpression.</param>
    /// <returns>LambdaExpression representing outer composed with inner</returns>
    public static Expression<Func<T, TResult>> Compose<T, TIntermediate, TResult>(this Expression<Func<TIntermediate, TResult>> fFn, Expression<Func<T, TIntermediate>> gFn) =>
        Expression.Lambda<Func<T, TResult>>(fFn.Body.Replace(fFn.Parameters[0], gFn.Body), gFn.Parameters[0]);    

    /// <summary>
    /// Replaces a sub-Expression with another Expression inside an Expression
    /// </summary>
    /// <param name="orig">The original Expression.</param>
    /// <param name="from">The from Expression.</param>
    /// <param name="to">The to Expression.</param>
    /// <returns>Expression with all occurrences of from replaced with to</returns>
    public static Expression Replace(this Expression orig, Expression from, Expression to) => new ReplaceVisitor(from, to).Visit(orig);
}

/// <summary>
/// Standard ExpressionVisitor to replace an Expression with another in an Expression.
/// </summary>
public class ReplaceVisitor : ExpressionVisitor {
    readonly Expression from;
    readonly Expression to;

    public ReplaceVisitor(Expression from, Expression to) {
        this.from = from;
        this.to = to;
    }

    public override Expression Visit(Expression node) => node == from ? to : base.Visit(node);
}

Now you can create your method that takes a lambda representing the field you want to test. It uses a local LambdaExpression as a template for the final result:

public static class Util {
    static Expression<Func<string, int>> TermOrderTemplateFn = p => (p == "Fall" ? 1 : p == "Spring" ? 2 : 3);
    public static Expression<Func<TRec, int>> TermsOrder<TRec>(Expression<Func<TRec, string>> selectorFn) =>
        TermOrderTemplateFn.Compose(selectorFn);
}

Now you can call the method in your expression, passing in a lambda representing the desired field (or field expression) to test:

var tmp = context.Terms.Include(x => x.StudentCourses).AsQueryable()
                .Where(x => x.StudentID == studentId && x.DepartmentID == departmentId)
                .OrderBy(x => x.AcademicYear)
                .ThenBy(Util.TermsOrder<Term>(p => p.TermRegistered));

Note: I am calling the type of context.Terms.First() Term but you would need to use the actual correct type name in the call to TermsOrder. You could also do TermsOrder((Term p) => ...) instead.

I would probably prefer to create a special version of ThenBy so you can use type inference to determine the record type:

public static class EFExt {
    static Expression<Func<string, int>> TermThenOrderTemplateFn = p => (p == "Fall" ? 1 : p == "Spring" ? 2 : 3);
    public static IOrderedQueryable<T> ThenByTerm<T>(this IOrderedQueryable<T> src, Expression<Func<T, string>> selectorFn) =>
        src.ThenBy(TermThenOrderTemplateFn.Compose(selectorFn));
}

Then you can use it directly:

var tmp = context.Terms.Include(x => x.StudentCourses).AsQueryable()
                .Where(x => x.StudentID == studentId && x.DepartmentID == departmentId)
                .OrderBy(x => x.AcademicYear)
                .ThenByTerm(p => p.TermRegistered);