Sorting by Dynamically Chosen Columns

These scenarios demonstrate how to perform sorts where the column being sorted by is provided by a string parameter.

See Basic Sorting for examples of sorting by a fixed column name.

Scenario Prototype

public interface IDynamicSortingScenario<TEmployeeSimple>
   where TEmployeeSimple : class, IEmployeeSimple, new()
{
    /// <summary>
    /// Insert a collection of Employee rows.
    /// </summary>
    void InsertBatch(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Sorts by a single column
    /// </summary>
    /// <param name="lastName">The last name filter.</param>
    IList<TEmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending);

    /// <summary>
    /// Sorts by a multiple columns
    /// </summary>
    /// <param name="lastName">The last name filter.</param>
    IList<TEmployeeSimple> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
        string sortByColumnB, bool isDescendingB);
}

For ORMs that don't directly support checking column names, a statically defined list is provided.

public static class Utilities
{
    public static ImmutableHashSet<string> EmployeeColumnNames { get; } = ImmutableHashSet.Create
        ("EmployeeKey", "FirstName", "MiddleName", "LastName", "Title", "OfficePhone",
        "CellPhone", "EmployeeClassificationKey");
}

For ORMs that use LINQ and IQueryable, these extensions are provided.

public static class DynamicSortingExtensions
{
    //Inspired by https://stackoverflow.com/a/31959568/5274

    static readonly MethodInfo s_OrderBy = typeof(Queryable).GetMethods()
        .Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition && m.GetParameters().Length == 2)
        .Single();

    static readonly MethodInfo s_OrderByDescending = typeof(Queryable).GetMethods()
        .Where(m => m.Name == "OrderByDescending" && m.IsGenericMethodDefinition && m.GetParameters().Length == 2)
        .Single();

    static readonly MethodInfo s_ThenBy = typeof(Queryable).GetMethods()
        .Where(m => m.Name == "ThenBy" && m.IsGenericMethodDefinition && m.GetParameters().Length == 2)
        .Single();

    static readonly MethodInfo s_ThenByDescending = typeof(Queryable).GetMethods()
        .Where(m => m.Name == "ThenByDescending" && m.IsGenericMethodDefinition && m.GetParameters().Length == 2)
        .Single();

    public static IOrderedQueryable<TSource> OrderBy<TSource>(this IQueryable<TSource> query, string propertyName)
    {
        return BuildQuery(s_OrderBy, query, propertyName);
    }

    public static IOrderedQueryable<TSource> OrderBy<TSource>(this IQueryable<TSource> query, string propertyName,
        bool isDescending)
    {
        if (isDescending)
            return BuildQuery(s_OrderByDescending, query, propertyName);
        else
            return BuildQuery(s_OrderBy, query, propertyName);
    }

    public static IOrderedQueryable<TSource> ThenBy<TSource>(this IQueryable<TSource> query, string propertyName,
        bool isDescending)
    {
        if (isDescending)
            return BuildQuery(s_ThenByDescending, query, propertyName);
        else
            return BuildQuery(s_ThenBy, query, propertyName);
    }

    public static IOrderedQueryable<TSource> OrderByDescending<TSource>(this IQueryable<TSource> query,
        string propertyName)
    {
        return BuildQuery(s_OrderByDescending, query, propertyName);
    }

    public static IOrderedQueryable<TSource> ThenBy<TSource>(this IQueryable<TSource> query, string propertyName)
    {
        return BuildQuery(s_ThenBy, query, propertyName);
    }

    public static IOrderedQueryable<TSource> ThenByDescending<TSource>(this IQueryable<TSource> query,
        string propertyName)
    {
        return BuildQuery(s_ThenByDescending, query, propertyName);
    }

    static IOrderedQueryable<TSource> BuildQuery<TSource>(MethodInfo method, IQueryable<TSource> query,
        string propertyName)
    {
        var entityType = typeof(TSource);

        var propertyInfo = entityType.GetProperty(propertyName);
        if (propertyInfo == null)
            throw new ArgumentOutOfRangeException(nameof(propertyName), "Unknown column " + propertyName);

        var arg = Expression.Parameter(entityType, "x");
        var property = Expression.Property(arg, propertyName);
        var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

        var genericMethod = method.MakeGenericMethod(entityType, propertyInfo.PropertyType);

        return (IOrderedQueryable<TSource>)genericMethod.Invoke(genericMethod, new object[] { query, selector })!;
    }
}

ADO.NET

ADO requires validation of sort columns against a statically defined list.

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumn))
        throw new ArgumentOutOfRangeException(nameof(sortByColumn), "Unknown column " + sortByColumn);

    var sortDirection = isDescending ? "DESC " : "";

    var sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        $"ORDER BY [{sortByColumn}] {sortDirection}";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@LastName", lastName);

        var results = new List<EmployeeSimple>();

        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                results.Add(new EmployeeSimple(reader));

        return results;
    }
}

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnA))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnA), "Unknown column " + sortByColumnA);
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnB))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnB), "Unknown column " + sortByColumnB);

    var sortDirectionA = isDescendingA ? "DESC " : "";
    var sortDirectionB = isDescendingB ? "DESC " : "";

    var sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        $"ORDER BY [{sortByColumnA}] {sortDirectionA}, [{sortByColumnB}] {sortDirectionB}";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@LastName", lastName);

        var results = new List<EmployeeSimple>();

        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                results.Add(new EmployeeSimple(reader));

        return results;
    }
}

Chain

Columns to be sorted by are passed in as strings, but checked against the list of columns at runtime to prevent SQL injection attacks. A SortExpression object is needed for reverse sorting.

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    var sortDirection = isDescending ? " DESC" : "";

    return m_DataSource.From<EmployeeSimple>(new { lastName })
        .WithSorting(sortByColumn + sortDirection)
        .ToCollection().Execute();
}

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    var sortDirectionA = isDescendingA ? " DESC" : "";
    var sortDirectionB = isDescendingB ? " DESC" : "";

    return m_DataSource.From<EmployeeSimple>(new { lastName })
        .WithSorting(
            sortByColumnA + sortDirectionA,
            sortByColumnB + sortDirectionB)
        .ToCollection().Execute();
}

Dapper

Dapper requires validation of sort columns against a statically defined list.

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumn))
        throw new ArgumentOutOfRangeException(nameof(sortByColumn), "Unknown column " + sortByColumn);

    var sortDirection = isDescending ? "DESC " : "";

    var sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        $"ORDER BY [{sortByColumn}] {sortDirection}";

    using (var con = OpenConnection())
        return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
}

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnA))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnA), "Unknown column " + sortByColumnA);
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnB))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnB), "Unknown column " + sortByColumnB);

    var sortDirectionA = isDescendingA ? "DESC " : "";
    var sortDirectionB = isDescendingB ? "DESC " : "";

    var sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        $"ORDER BY [{sortByColumnA}] {sortDirectionA}, [{sortByColumnB}] {sortDirectionB}";

    using (var con = OpenConnection())
        return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
}

DbConnector

DbConnector requires validation of sort columns against a statically defined list.

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumn))
        throw new ArgumentOutOfRangeException(nameof(sortByColumn), "Unknown column " + sortByColumn);

    var sortDirection = isDescending ? "DESC " : "";

    var sql = @$"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey 
        FROM HR.Employee e 
        WHERE e.LastName = @lastName
        ORDER BY [{sortByColumn}] {sortDirection}";

    return DbConnector.ReadToList<EmployeeSimple>(sql, new { lastName }).Execute();
}

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnA))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnA), "Unknown column " + sortByColumnA);
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnB))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnB), "Unknown column " + sortByColumnB);

    var sortDirectionA = isDescendingA ? "DESC " : "";
    var sortDirectionB = isDescendingB ? "DESC " : "";

    var sql = @$"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey 
        FROM HR.Employee e 
        WHERE e.LastName = @lastName
        ORDER BY [{sortByColumnA}] {sortDirectionA}, 

Entity Framework 6

Entity Framework does not natively support sorting by strings. However, this can be acomplished with DynamicSortingExtensions.

public IList<Employee> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderBy(sortByColumn, isDescending).ToList();
}

public IList<Employee> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderBy(sortByColumnA, isDescendingA)
            .ThenBy(sortByColumnB, isDescendingB)
            .ToList();
}

Entity Framework Core

EF Core does not natively support sorting by strings. However, this can be acomplished with DynamicSortingExtensions.

public IList<Employee> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderBy(sortByColumn, isDescending).ToList();
}

public IList<Employee> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderBy(sortByColumnA, isDescendingA)
            .ThenBy(sortByColumnB, isDescendingB)
            .ToList();
}

LINQ to DB

LinqToDB does not natively support sorting by strings. However, this can be acomplished with DynamicSortingExtensions.

public IList<Employee> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    using (var db = new OrmCookbook())
        return db.Employee.Where(x => x.LastName == lastName)
            .OrderBy(sortByColumn, isDescending).ToList();
}

public IList<Employee> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    using (var db = new OrmCookbook())
        return db.Employee.Where(x => x.LastName == lastName)
            .OrderBy(sortByColumnA, isDescendingA)
            .ThenBy(sortByColumnB, isDescendingB)
            .ToList();
}

LLBLGen Pro

LLBLGen Pro does natively support sorting by strings but it requires a little bit of verbose code using the low-level API of older versions. This is illustrated in the code below in the first method. The second method shows the alternative using the DynamicSortingExtensions.

public IList<EmployeeEntity> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    using(var adapter = new DataAccessAdapter())
    {
        // We use queryspec here which can use the low-level API elements we need to use to accomplish sorting
        // on a string. The low-level API uses entity fields and we obtain the field we need from a dummy entity instance
        // we create. 
        var fieldToSort = new EmployeeEntity().Fields[sortByColumn];
        // fieldToSort will be null if sortByColumn isn't found. The query will therefore end in an exception if that's
        // the case. For this particular test, it's the desired outcome so no null check is performed.
        var q = new QueryFactory().Employee.Where(EmployeeFields.LastName.Equal(lastName))
                                  .OrderBy(isDescending
                                               ? fieldToSort.Descending()
                                               : fieldToSort.Ascending());
        return (IList<EmployeeEntity>)adapter.FetchQuery(q);

        // Alternatively we could have used the DynamicSortingExtensions in a linq query:
        // return new LinqMetaData(adapter).Employee.Where(x => x.LastName == lastName)
        //                              .OrderBy(sortByColumn, isDescending)
        //                              .ToList();
    }
}

public IList<EmployeeEntity> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
                                    string sortByColumnB, bool isDescendingB)
{
    using(var adapter = new DataAccessAdapter())
    {
        return new LinqMetaData(adapter).Employee.Where(x => x.LastName == lastName)
                                        .OrderBy(sortByColumnA, isDescendingA)
                                        .ThenBy(sortByColumnB, isDescendingB)
                                        .ToList();
    }
}

NHibernate

NHibernate does not support sorting by strings.

RepoDb

Columns to be sorted by are passed in as a collection of OrderField objects. They checked against the list of columns at runtime to prevent SQL injection attacks.

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    var sort = new[] { new OrderField(sortByColumn, isDescending ? Order.Descending : Order.Ascending) };
    return Query(x => x.LastName == lastName, orderBy: sort).AsList();
}

public IList<EmployeeSimple> SortBy(string lastName, string sortByColumnA, bool isDescendingA, string sortByColumnB, bool isDescendingB)
{
    var sort = new[] {
        new OrderField(sortByColumnA, isDescendingA ? Order.Descending : Order.Ascending),
        new OrderField(sortByColumnB, isDescendingB ? Order.Descending : Order.Ascending)
    };
    return Query(x => x.LastName == lastName, orderBy: sort).AsList();
}

ServiceStack

ServiceStack requires validation of sort columns against a statically defined list. ServiceStack will attempt to detect some instances of SQL injection, but potentially dangerous expressions are allowed.

public IList<Employee> SortBy(string lastName, string sortByColumn, bool isDescending)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumn))
        throw new ArgumentOutOfRangeException(nameof(sortByColumn), "Unknown column " + sortByColumn);

    var sortDirection = isDescending ? " DESC " : "";

    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        return db.Select(db.From<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(sortByColumn + sortDirection)).ToList();
    }
}

public IList<Employee> SortBy(string lastName, string sortByColumnA, bool isDescendingA,
    string sortByColumnB, bool isDescendingB)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnA))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnA), "Unknown column " + sortByColumnA);
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnB))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnB), "Unknown column " + sortByColumnB);

    var sortDirectionA = isDescendingA ? " DESC " : "";
    var sortDirectionB = isDescendingB ? " DESC " : "";

    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        return db.Select(db.From<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(sortByColumnA + sortDirectionA + "," + sortByColumnB + sortDirectionB)).ToList();
    }
}