Dynamic Linq to Entities Orderby with Pagination

Question

I have a SQL table with over 800k records that need to be displayed on the web in pages of 25 results. I need to be able to search and sort these results from the table, but because the table is so large I cannot pull all the results to an IEnumerable before filtering/sorting (I was doing this before and it worked, but it is now incredibly slow doing the initial pull).

I've figured out the search, but the sort is really messing me up. I've spent hours researching it, but can't find any solutions that work before the .Skip().Take().

I need to be able to do something like this:

string sortField = "Name"; //just for example
string sortDirection = "descending"; //also for example
List<People> = (from s in db.People
                orderby sortField sortDirection
                select s).Skip((page - 1) * pageSize).Take(pageSize).ToList();

The sortable columns in People can be DateTime, ints, or strings, so my attempts to do something like

orderby (
    currentSort == "Name" ? s.Name : 
    currentSort = "SignUpDate" ? s.SignupDate : s.Id
)

were in vain, as the program complains about mixing types.

Is there anything that can be done to make this work? Thanks in advance for any help or leads!

Answer 1

You can use the following custom extension method, which builds OrderBy(Descending) call dynamically using the System.Linq.Expressions.Expression class (similar to How to use a string to create a EF order by expression?):

public static partial class QueryableExtensions
{
    public static IOrderedQueryable<T> OrderByMember<T>(this IQueryable<T> source, string memberPath, bool descending)
    {
        var parameter = Expression.Parameter(typeof(T), "item");
        var member = memberPath.Split('.')
            .Aggregate((Expression)parameter, Expression.PropertyOrField);
        var keySelector = Expression.Lambda(member, parameter);
        var methodCall = Expression.Call(
            typeof(Queryable), descending ? "OrderByDescending" : "OrderBy", 
            new[] { parameter.Type, member.Type },
            source.Expression, Expression.Quote(keySelector));
        return (IOrderedQueryable<T>)source.Provider.CreateQuery(methodCall);
    }
}

like this:

var people = db.People
    .OrderByMember(sortField, sortDirection == "descending")
    .Skip((page - 1) * pageSize).Take(pageSize)
    .ToList();