Sort enum column based on language

Question

Say I have the following simple setup:

public enum Status
{
    Pending  = 0,
    Accepted = 1,
    Rejected = 2
}

public class MyEntity
{
    public Status Status { get; set; }
}

...and I desire the following ascending sorting for Status for two different languages:

Language 1: 1, 0, 2
Language 2: 0, 1, 2

Is there a simple way to do this in entity framework? I feel like this is probably a common scenario.

I'm thinking the only way is to maintain a separate table with all the translations for Status... then sort by the name column in that table based on the users current language. I wanted to see if anyone had any other ideas though.

Answer 1

You could write a query without having a separate table, but it's not pretty. Nor is it very flexible. Since EF relies on well known methods to map to the equivalent SQL function, you can't interject custom functions. However, you can manually set the order using a let:

var query = from e in MyEntity
            let sortOrder = UserLanguage == Language1 // Handle Language 1 Sort
                                ? e.Status == Pending 
                                      ? 1 : e.Status == Accepted ? 0 : 2
                           : e.Status == Pending // Handle Language 2 sort
                               ? 0 : e.Status == Accepted ? 1 : 2                                                                
            orderby sortOrder
            select e

And this is only for two language. Another way I could think of is to write the expression tree yourself. This would have the advantage that you can split the logic for each language. We can extract the ternary condition logic and place them in an extension static class. Here's a sample of what it could look like:

public static class QuerySortExtension
{
    private static readonly Dictionary<string, Expression<Func<MyEntity, int>>> _orderingMap;
    private static readonly Expression<Func<MyEntity, int>> _defaultSort;

    public static IOrderedQueryable<MyEntity> LanguageSort(this IQueryable<MyEntity> query, string language)
    {
        Expression<Func<MyEntity, int>> sortExpression;
        if (!_orderingMap.TryGetValue(language, out sortExpression))
            sortExpression = _defaultSort;

        return query.OrderBy(sortExpression);
    }

    static QuerySortExtension()
    {
        _orderingMap = new Dictionary<string, Expression<Func<MyEntity, int>>>(StringComparer.OrdinalIgnoreCase) {
            { "EN", e => e.Status == Status.Pending ? 1 : e.Status == Status.Accepted ? 0 : 2 },
            { "JP", e => e.Status == Status.Pending ? 2 : e.Status == Status.Accepted ? 1 : 0 }
        };

        // Default ordering
        _defaultSort = e => (int)e.Status;
    }

}

And you can use the following method this way:

var entities = new[] { 
    new MyEntity { Status = Status.Accepted }, 
    new MyEntity { Status = Status.Pending }, 
    new MyEntity { Status = Status.Rejected } 
}.AsQueryable();

var query = from e in entities.LanguageSort("EN")
            select e;
var queryJp = from e in entities.LanguageSort("JP")
              select e;

Console.WriteLine("Sorting with EN");
foreach (var e in query)
    Console.WriteLine(e.Status);

Console.WriteLine("Sorting with JP");
foreach (var e in queryJp)
    Console.WriteLine(e.Status);

Which output the following:

Sorting with EN
Accepted
Pending
Rejected
Sorting with JP
Rejected
Accepted
Pending