c# - Order by enum names on sql server

Question

I'd like to get top 10 records from the table ordered by enum name. There is a following enum:

public enum OrderStatus {
    New = 1,
    Started = 2,
    Completed = 3
}

There is no way the following would work, as it will return results, ordered by numbers (1,2,3) instead of status names ("New", "Started", "Completed").

List<Order> orders = context.orders.OrderBy(x => x.Status).Take(10);

Though it is possible to convert enum values to names in C#, there is no way the following will be translated to SQL:

List<Order> orders = context.orders.OrderBy(x => Enum.Parse(typeof(Order), x.Status.ToString())).Take(10);

It is important to do sorting and filtering on the SQL server, not in code. The table contains an enormous amount of data, so it will take ages for it to be retrieved.

I don't expect you to write actual code for me. Just suggesting an approach will do.

UPD: Yeah, there is a 6 year old answer suggesting obvious things like to add a table to the db. I am looking for a c# solution though. There is no way I can modify the db, create 10 FKs and mirror all 10 enums from backend to the db just because of a silly thing like sorting 1 entity. This will not do. Entity Framework sort by Enum value alphabetically

UPD#2: For those who are claiming it is impossible to achieve, I'll present the following SQL:

SELECT TOP (10)
    *,
    case
        when [Status] = 1 then 'New'
        when [Status] = 2 then 'Started'
        when [Status] = 3 then 'Completed'
    end as [StatusOrder]
FROM [Orders]
order by [StatusOrder]

It does exactly what I need. So, is there a way to make EF generate something similar?

question from:https://stackoverflow.com/questions/65883544/order-by-enum-names-on-sql-server

Answer 1

Simplest way:

var query = context.orders.OrderBy(x => (int)x.Status == 1 ? "New" :
                  (int)x.Status == 2 ? "Started" : "Completed").Take(10);

Universal way:

Install this package: https://github.com/axelheer/nein-linq

Write the following helper method:

public static class QueryableExtensions
{
   [InjectLambda]
   public static string AsString(this OrderStatus status)
   {
      throw new NotImplementedException();
   }

   static Expression<Func<OrderStatus, string>> AsString()
   {
      return e => (int)e == 1 ? "New" :
                  (int)e == 2 ? "Started" : "Completed"     
   }
}

Then use in query:

context.orders
  .ToInjectable()
  .OrderBy(x => x.Status.AsString()).Take(10);

Also consider to use not strings but integers which should specify order.