Order by enum description

Question

I am working on an ASP.NET MVC projet using EF code first, and I am facing a situation where I need to order by an enum description:

public partial class Item
{
    public enum MyEnumE
    {
        [Description("description of enum1")]
        Enum1,
        [Description("description of enum2")]
        Enum2,
        ...
    }

    public MyEnumE MyEnum { get; set; }
}

Here is the Search and SortAndPaginate functions:

public async Task<IPagedList<Item>> Search(ItemCriteria criteria, SortableTypeE sortName, SortOrder.TypeE sortOrder, int pageNb)
    {
        var itemFilter = GenerateFilter(criteria);
        var items = entities.Items.Where(itemFilter);

        return await SortAndPaginate(items, sortName, sortOrder, pageNb);
    }

    private async Task<IPagedList<Item>> SortAndPaginate(IQueryable<Item> items, SortableTypeE sortName, SortOrder.TypeE sortOrder, int pageNb)
    {
        IOrderedQueryable<Item> result = null;

        switch (sortName)
        {
            ...
            case SortableTypeE.Type:
                result = sortOrder == SortOrder.TypeE.ASC
                    ? items.OrderBy(i => i.MyEnum.GetDescription())
                    : items.OrderByDescending(i => i.MyEnum.GetDescription());
                result = result.ThenBy(i => i.SomeOtherProperty);
                break;
            ...
        }

        if (result != null)
        {
            return await result.ToPagedListAsync(pageNb, 10);
        }

        return PagedListHelper.Empty<Item>();
    }

The problem is that the Item table can be quite huge.
I thought about calling ToListAsync right after entities.Items.Where(itemFilter) but this will get back all filtered items although I only need one page. Does not sound like a good idea.

But if I don't do that EF won't know about GetDescription() mathod and I can only think about two solutions:
- Change my database column to a string (the enum description) instead of the enum itself (but sounds like a hack to me)
- Or alphabetically order MyEnumE components directly in the enum declaration (seems dirty and quite unmaintainable too)

I'm quite stuck since I'm concerned about performances if I call ToListAsync right after filtering, all other solutions seem dirty, and I absolutely need a IPagedList returned from the Search method.

Would anyone have an idea about how to deal with this issue ?

Thanks a lot.

UPDATE

Here is the GetDescription method (can change it if necessary):

public static string GetDescription(this Enum e)
{
    FieldInfo fi = e.GetType().GetField(e.ToString());
    DescriptionAttribute[] attributes = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
    if (attributes.Length > 0)
        return attributes[0].Description;
    else
        return e.ToString();
}

SOLUTIONS

I'll finally go for Ivan Stoev's suggestion because my project is mainly based on Linq (using Linq instead of stored procedures etc.), so this solution seems more suitable to my particular case than creating reference tables.

However Niyoko Yuliawan's and Michael Freidgeim's are also really good answers to me, anyone reading this post and having a more database approach should go for their solutions ;)

Thanks a lot to all of you.

Answer 1

I would go with dynamic expression. It's more flexible and can easily be changed w/o affecting the database tables and queries.

However, instead of sorting by description strings in the database, I would create ordered map in memory, associating int "order" value with each enum value like this:

public static class EnumHelper
{
    public static Expression<Func<TSource, int>> DescriptionOrder<TSource, TEnum>(this Expression<Func<TSource, TEnum>> source)
        where TEnum : struct
    {
        var enumType = typeof(TEnum);
        if (!enumType.IsEnum) throw new InvalidOperationException();

        var body = ((TEnum[])Enum.GetValues(enumType))
            .OrderBy(value => value.GetDescription())
            .Select((value, ordinal) => new { value, ordinal })
            .Reverse()
            .Aggregate((Expression)null, (next, item) => next == null ? (Expression)
                Expression.Constant(item.ordinal) :
                Expression.Condition(
                    Expression.Equal(source.Body, Expression.Constant(item.value)),
                    Expression.Constant(item.ordinal),
                    next));

        return Expression.Lambda<Func<TSource, int>>(body, source.Parameters[0]);
    }

    public static string GetDescription<TEnum>(this TEnum value)
        where TEnum : struct
    {
        var enumType = typeof(TEnum);
        if (!enumType.IsEnum) throw new InvalidOperationException();

        var name = Enum.GetName(enumType, value);
        var field = typeof(TEnum).GetField(name, BindingFlags.Static | BindingFlags.Public);
        return field.GetCustomAttribute<DescriptionAttribute>()?.Description ?? name;
    }
}

The usage would be like this:

case SortableTypeE.Type:
    var order = EnumHelper.DescriptionOrder((Item x) => x.MyEnum);
    result = sortOrder == SortOrder.TypeE.ASC
        ? items.OrderBy(order)
        : items.OrderByDescending(order);
    result = result.ThenBy(i => i.SomeOtherProperty);
    break;

which would generate expression like this:

x => x.MyEnum == Enum[0] ? 0 :
     x.MyEnum == Enum[1] ? 1 :
     ...
     x.MyEnum == Enum[N-2] ? N - 2 :
     N - 1;

where 0,1,..N-2 is the corresponding index in the value list sorted by description.

Answer 2

Alternative 1

You can do it by projecting enum into custom value and sort by it.

Example:

items
    .Select(x=> new 
    {
        x,
        Desc = (
            x.Enum == Enum.One ? "Desc One" 
            : x.Enum == Enum.Two ? "Desc Two" 
            ... and so on)
    })
    .OrderBy(x=>x.Desc)
    .Select(x=>x.x);

Entity framework then will generate SQL something like this

SELECT
    *
FROM
    YourTable
ORDER BY
    CASE WHEN Enum = 1 THEN 'Desc One'
    WHEN Enum = 2 THEN 'Desc Two'
    ...and so on
    END

If you have a lot of query like this, you can create extension method

public static IQueryable<Entity> OrderByDesc(this IQueryable<Entity> source)
{
    return source.Select(x=> new 
    {
        x,
        Desc = (
            x.Enum == Enum.One ? "Desc One" 
            : x.Enum == Enum.Two ? "Desc Two" 
            ... and so on)
    })
    .OrderBy(x=>x.Desc)
    .Select(x=>x.x);
}

And call it when you need it

var orderedItems = items.OrderByDesc();

---

Alternative 2

Another alternative solution is to create additional table that map enum value to enum description and join your table to this table. This solution will be more performant because you can create index on enum description column.

---

Alternative 3

If you want dynamic expression based on your enum description attribute, you can build yourself

Helper Class

public class Helper
{
    public MyEntity Entity { get; set; }
    public string Description { get; set; }
}

Get dynamically built expression

public static string GetDesc(MyEnum e)
{
    var type = typeof(MyEnum);
    var memInfo = type.GetMember(e.ToString());
    var attributes = memInfo[0].GetCustomAttributes(typeof(DescriptionAttribute),
        false);
    return ((DescriptionAttribute)attributes[0]).Description;
}

private static Expression<Func<MyEntity, Helper>> GetExpr()
{
    var descMap = Enum.GetValues(typeof(MyEnum))
        .Cast<MyEnum>()
        .ToDictionary(value => value, GetDesc);

    var paramExpr = Expression.Parameter(typeof(MyEntity), "x");
    var expr = (Expression) Expression.Constant(string.Empty);
    foreach (var desc in descMap)
    {
        // Change string "Enum" below with your enum property name in entity
        var prop = Expression.Property(paramExpr, typeof(MyEntity).GetProperty("Enum")); 
        expr = Expression.Condition(Expression.Equal(prop, Expression.Constant(desc.Key)),
            Expression.Constant(desc.Value), expr);
    }


    var newExpr = Expression.New(typeof(Helper));

    var bindings = new MemberBinding[]
    {
        Expression.Bind(typeof(Helper).GetProperty("Entity"), paramExpr),
        Expression.Bind(typeof(Helper).GetProperty("Description"), expr)
    };

    var body = Expression.MemberInit(newExpr, bindings);

    return (Expression<Func<MyEntity, Helper>>) Expression.Lambda(body, paramExpr);
}

Call it like this

var e = GetExpr();
items.Select(e)
    .OrderBy(x => x.Description)
    .Select(x => x.Entity);