EF Core order by string column name
Tags: api, csharp, dotnetcore
Published: 2023-06-04 21:22:05

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](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.orderby?view=net-7.0) and [OrderByDescending](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.orderdescending?view=net-7.0) 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.

```csharp
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.

```csharp
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.

```csharp
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](https://github.com/karenpayneoregon/ef-core-order-by/blob/master/NorthWindExampleApp/Models/ContactType.cs) as follows.

```csharp
public virtual ContactType ContactTypeIdentifierNavigation { get; set; }
```

Then open ContactType model and we have a property

```csharp
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

```csharp
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](https://github.com/karenpayneoregon/ef-core-order-by)
- Run [northWind.sql](https://github.com/karenpayneoregon/ef-core-order-by/blob/master/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.