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?

Does this answer your question? [Entity Framework sort by Enum value alphabetically](https://stackoverflow.com/questions/23799273/entity-framework-sort-by-enum-value-alphabetically) — Franz Gleichmann, Jan 25 '21 at 11:09
IMHO There's no other way that creating custom procedure on SQL Server that will map your integers representation of this enum to string (VARCHAR) — mrogal.ski, Jan 25 '21 at 11:09
There are no enums in SQL. There are integer/string values, or lookup tables with an `Order` column that could be used for ordering — Panagiotis Kanavos, Jan 25 '21 at 11:09
@Mateusz there is - use a lookup table. Which is far better than handling an `enum` as a quasi-class. — Panagiotis Kanavos, Jan 25 '21 at 11:10
Nope, there is no way this will work for me as I cannot modify the db. https://stackoverflow.com/questions/23799273/entity-framework-sort-by-enum-value-alphabetically — Hirasawa Yui, Jan 25 '21 at 11:11
@HirasawaYui then you can't order by name on the server. Because that name doesn't even exist in the database. If you used SQL, you could use a table variable or a row constructor to create a "lookup table" in the query itself and join it to the rest of the data. You may be able to use `FromSqlRaw` to do something similar — Panagiotis Kanavos, Jan 25 '21 at 11:11
@PanagiotisKanavos Sorry, I've stated this unclearly but my main point was that enum values will most likely written as integer representation and the only way to retrieve this in a way OP want's it would be to map ( either by lookup table or some custom table ) these integer values to names. Hope this was much clearer — mrogal.ski, Jan 25 '21 at 11:17

Svyatoslav Danyliv · Accepted Answer · 2021-01-25T19:36:03.300

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.

Order by enum names on sql server

1 Answers1