How to order a list of entities by a custom order?

Question

I have a list of entities that could be represented like that:

Type | Property A | Property B | ... 
--------------------------------------
A    | ABC        | 123        | ...
A    | ABC        | 123        | ...
D    | ABC        | 123        | ...
D    | ABC        | 123        | ...
B    | ABC        | 123        | ...
C    | ABC        | 123        | ...

Type is a string and can only be one of 4 distinct values (e. g A, B, C, D) I need to order these entities by Type but with a custom order (e. g. A, D, B, C).

I tried something like this:

var orderType = new Dictionary<string, int>() {
                    { "A", 0 },
                    { "D", 1 },
                    { "B", 2 },
                    { "C", 3 }
                };
return db.MyEntity
         .OrderBy(x => orderType[x.Type])
         .ToList();

But I get the following error:

LINQ to Entities does not recognize the method 'Int32 get_Item(System.String)' method, and this method cannot be translated into a store expression.

I don't understand this error and don't know what to do with this OrderBy. Could you please tell me how to order these entities by Type with a custom order?

Answer 1

I don't understand this error

It's because LINQ TO SQL cannot translate Int32 get_Item(System.String) of Dictionary into an equivalent operation in SQL.

---

A very simple way to solve it is to bring entities in memory by AsEnumerable() :

return db.MyEntity.AsEnumerable().
         .OrderBy(x => orderType[x.Type])
         .ToList();