EF Core order by string column name

#api #csharp #dotnetcore

In this article, learn how to order data returned from a database using Microsoft Entity Framework Core (EF Core).

To order data in EF Core a developer uses OrderBy and OrderByDescending extension methods which accept a column to order data.

Example, using Microsoft NorthWind sample database return customers with several inner joins and a descending order on a navigation column ContactTitle.

public static async Task<List<Customers>> ConventionalOrderByOnNavigation()
{
    await using var context = new Context();

    return await context.Customers
        .Include(c => c.CountryIdentifierNavigation)
        .Include(c => c.Contact)
        .Include(c => c.ContactTypeIdentifierNavigation)
        .OrderByDescending(c => c.ContactTypeIdentifierNavigation.ContactTitle)
        .ToListAsync();
}

The above is fine with the order is known ahead of time but suppose there is code logic to determine how to order results or there is a requirement to allow users to select the order by.

This can be done with the following extension methods.

public static class QueryableExtensions
{
    public static IOrderedQueryable<T> OrderByColumn<T>(this IQueryable<T> source, string columnPath) 
        => source.OrderByColumnUsing(columnPath, "OrderBy");

    public static IOrderedQueryable<T> OrderByColumnDescending<T>(this IQueryable<T> source, string columnPath) 
        => source.OrderByColumnUsing(columnPath, "OrderByDescending");

    public static IOrderedQueryable<T> ThenByColumn<T>(this IOrderedQueryable<T> source, string columnPath) 
        => source.OrderByColumnUsing(columnPath, "ThenBy");

    public static IOrderedQueryable<T> ThenByColumnDescending<T>(this IOrderedQueryable<T> source, string columnPath) 
        => source.OrderByColumnUsing(columnPath, "ThenByDescending");

    private static IOrderedQueryable<T> OrderByColumnUsing<T>(this IQueryable<T> source, string columnPath, string method)
    {
        var parameter = Expression.Parameter(typeof(T), "item");
        var member = columnPath.Split('.')
            .Aggregate((Expression)parameter, Expression.PropertyOrField);
        var keySelector = Expression.Lambda(member, parameter);
        var methodCall = Expression.Call(typeof(Queryable), method, new[] 
                { parameter.Type, member.Type },
            source.Expression, Expression.Quote(keySelector));

        return (IOrderedQueryable<T>)source.Provider.CreateQuery(methodCall);
    }
}

Now we can alter the first code sample as follows.

public static async Task<List<Customers>> ConventionalOrderByOnNavigationFlexible()
{
    await using var context = new Context();

    return await context.Customers
        .Include(c => c.CountryIdentifierNavigation)
        .Include(c => c.Contact)
        .Include(c => c.ContactTypeIdentifierNavigation)
        .OrderByColumnDescending("ContactTypeIdentifierNavigation.ContactTitle")
        .ToListAsync();
}

The key is in the above is to find the property in the Customers model for ContactType as follows.

public virtual ContactType ContactTypeIdentifierNavigation { get; set; }

Then open ContactType model and we have a property

public string ContactTitle { get; set; }

Concatenate the the navigation and property name to create the order by "ContactTypeIdentifierNavigation.ContactTitle"

Use in your project

After cloning the source repository, copy the project OrderLibrary to your Visual Studio solution and add a reference to a project.

Note
Since strings are being used the above can break if a model changes. If a model changes be sure to update the order by strings.

Provided code samples

NorthWindExampleApp has examples for several models with various permutations using SQL-Server.
NorthWindExampleApp2 has an example for SqlLite with a ThenBy besides a main order by

List<Product> products  = await context
    .Products
    .Include(p => p.Category)
    .OrderByColumn("Category.Name")
    .ThenByColumn("Name")
    .ToListAsync();

NorthWindExampleApp3 is a windows project meant to be a visual of what is possible. With little effort this can be applied to just about any desktop or web project.

Source code

Clone the following GitHub repository
Run northWind.sql in the root folder of the Visual Studio solution to create the database.

Explore

Take time to explore supporting code used in the solution as there may be things that you might find useful.

Top comments (5)

Morten Ørgaard • Sep 16

I think this is amazing, however I made a minor update to the extension

using System.Linq.Expressions;

namespace admin.bhipone.com.Extensions;

public static class QueryableExtensions
{
    public static IOrderedQueryable<T> OrderByColumn<T>(this IQueryable<T> source, string columnPath, string direction = "asc")
    {
        return direction.ToLower() switch
        {
            "desc" => source.OrderByColumnUsing(columnPath, "OrderByDescending"),
            _ => source.OrderByColumnUsing(columnPath, "OrderBy")
        };
    }

    public static IOrderedQueryable<T> ThenByColumn<T>(this IOrderedQueryable<T> source, string columnPath, string direction = "asc") 
    {
        return direction.ToLower() switch
        {
            "desc" => source.OrderByColumnUsing(columnPath, "ThenByDescending"),
            _ => source.OrderByColumnUsing(columnPath, "ThenBy")
        };
    }

    private static IOrderedQueryable<T> OrderByColumnUsing<T>(this IQueryable<T> source, string columnPath, string method)
    {
        var parameter = Expression.Parameter(typeof(T), "item");
        var member = columnPath.Split('.')
            .Aggregate((Expression)parameter, Expression.PropertyOrField);
        var keySelector = Expression.Lambda(member, parameter);
        var methodCall = Expression.Call(typeof(Queryable), method, new[] 
                { parameter.Type, member.Type },
            source.Expression, Expression.Quote(keySelector));

        return (IOrderedQueryable<T>)source.Provider.CreateQuery(methodCall);
    }
}

The "asc" value is not really needed, it could be just empty, but I like it when it's there.

Karen Payne • Sep 18

Thanks for sharing!!!

Peter Heutink • Nov 25

public static IOrderedQueryable<T> OrderByColumns<T>(this IQueryable<T> source, List<string> columnPaths)
{
    if (columnPaths == null || columnPaths.Count == 0)
    {
        throw new ArgumentException("Invalid column path", nameof(columnPaths));
    }

    var orderedQuery = source.OrderByColumn(columnPaths[0]);

    for (var i = 1; i < columnPaths.Count; i++)
    {
        orderedQuery = orderedQuery.ThenByColumn(columnPaths[i]);
    }

    return orderedQuery;
}