8

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?

Alex
  • 2,927
  • 8
  • 37
  • 56
  • The reason this doesn't work is because EntityFramework will need to send teh Dictionary over to SQL in some way, which it cannot do efficiently. – jessehouwing Apr 03 '15 at 10:12
  • You can achieve this by telling EntityFramework how to translate your dictionary to a function based on predicates. http://stackoverflow.com/a/16839341/736079 – jessehouwing Apr 03 '15 at 10:13

1 Answers1

5

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();
Perfect28
  • 11,089
  • 3
  • 25
  • 45