Entity Framework sort by Enum value alphabetically

Question

I have an entity called Comment, which has an enum property of type CommentType:

public class Comment
{
    public virtual Guid Id { get; private set; }
    public virtual CommentType CommentType { get; set; }
    // other prop removed for simplicity
}

public enum CommentType
{
    Comment,
    Correction,
    Improvement,
    BugFix,
    NewFeauture,
    Other 
}

I need to select the comments from database by the alphabetically value of the CommentType enum, something like

_db.Comments.OrderBy(p => p.CommentType)

However, the Enum values are treated as integers, and the sort will not work alphabetically correctly.

Is there any way to add some attributes / metadata to the Enum values to make them sort correctly alphabetically?

One solution will be to assign the integer value to enum values, but i already have many database records that will need to be updated. And this solution is not good for new added enum values.

public enum CommentType
{
    Comment = 2,
    Correction = 3,
    Improvement = 4,
    BugFix = 1,
    NewFeauture = 5,
    Other = 6 
}

@DoanCuong, to make this work you have to call _db.Comments.ToList() first, like Machet mentioned below. Otherwise it will cause runtime error. — tala9999, Feb 28 '21 at 15:08

score 2 · Answer 1 · answered Dec 13 '21 at 05:45

This idea of a table is nice of course, especially when the enum has many values and is likely to get new ones. However, when enum values are added both the code and the database need to be maintained. In general when the enum is volatile I would not use an enum but only the table. But when it is not likely to change much, you could also consider to stick with the enum simply write out the order instruction:

_db.Comments.OrderBy(p => 
    p.CommentType == CommentType.Comment ? "Comment" :
    p.CommentType == CommentType.Correction ? "Correction" :
    p.CommentType == CommentType.Improvement? "Improvement" :
    .... :
    "ZZZ")

score 1 · Answer 2 · edited May 23 '17 at 12:14

Currently your enum name is not known at database level so in my opinion you have two options.

Use value on server side:

_db.Comments.ToList().OrderBy(p => p.CommentType.ToString())

Add value on database side: You should create table that will contain comment type names. And then you can create foreign key between comments and commentTypeNames and use simple select:

_db.Comments.OrderBy(c => c.CommentTypeNames.Name)

or just make join:

_db.Comments
    .Join(
        _db.CommentTypeNames, 
        c => c.CommentType, 
        ctn => ctn.CommentType,
        (c, ctn) => new { Comment = c, CommentName = ctn })
    .OrderBy(g => g.CommentName.Name)
    .Select(g => g.Comment);

There are also computed columns. I never used those though so I dont have experience. Maybe you could add column that will be resolved to string by CASE statements on database. However I am not sure how will this affect performance/maintainability. For more info you could check http://geekswithblogs.net/DavidPaquette/archive/2012/09/23/calculated-columns-in-entity-framework-code-first-migrations.aspx

For performance reasons I would advise to take option 2, it's better to do such things in database.

I assume that you are using code first so maybe this post will be helpful for you: EF5 Code First Enums and Lookup Tables

I was hoping for an Attribute which `EntityFramework` could interpret and send a specific order by with priority based on value — Catalin, May 22 '14 at 11:33
@RaraituL I doubt that there is such attribute. I have added short info about computed column to my answer, maybe it will help you — Machet, May 22 '14 at 13:34
For method 1: to make it work you have to call `ToList()` first, which is unrealistic if you have a large amount of records because the sort occurs in code, not in database. — tala9999, Feb 28 '21 at 15:05

Andrew · Answer 3 · 2014-05-22T09:44:07.870

0

As an alternative you may convert CommentType property in your Comment class into foreign key 'CommentTypeId' pointing at CommentType(Id, Name) table, that has your Id values and corresponding text Name.

This will let you order by text easily.

edited May 22 '14 at 09:44

answered May 22 '14 at 07:47

Andrew

3,648
1
15
29

Entity Framework sort by Enum value alphabetically

3 Answers3

Linked