DEV Community

Cover image for EF Core order by string column name
Karen Payne
Karen Payne

Posted on

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();
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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; }
Enter fullscreen mode Exit fullscreen mode

Then open ContactType model and we have a property

public string ContactTitle { get; set; }
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode
  • 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

Explore

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

Top comments (5)

Collapse
 
morten_rgaard_5cedbc0710 profile image
Morten Ørgaard •

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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

Collapse
 
karenpayneoregon profile image
Karen Payne •

Thanks for sharing!!!

Collapse
 
htk360 profile image
Peter Heutink • 
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;
}
Enter fullscreen mode Exit fullscreen mode
Collapse
 
htk360 profile image
Peter Heutink •

Great extension!
I added the ability to pass a list of columns to be ordered by.

Collapse
 
jeudry_peapea_3fb12c4d profile image
Jeudry Peña Peña •

This is crazy good, i needed this so much

Read next

blamsa0mine profile image

🚀Build an Image Processing API with Express, Multer, Potrace, and Sharp

A0mineTV -

greenfieldcoder profile image

Adding Revision Support to Entities in Entity Framework Core

Jimmy -

ikoichi profile image

How to validate your Next.js API with Zod and Typescript

Luca Restagno -

carterharrison profile image

Building a Real-Time Financial Market Analyst API with LLMs

Carter Harrison -