How to sort with a CASE statement in an EntityDataSource?

Question

I'm using a CASE statement in my EntityDataSource to do custom sorting. Consider the following code:

<asp:EntityDataSource ID="myEntityDataSource" runat="server" 
    ConnectionString="name=MySQLEntities1" 
    DefaultContainerName="MySQLEntities1" 
    EnableFlattening="False" 
    EntitySetName="Persons" 
    EntityTypeFilter="Persons"
    OrderBy="it.[Pack], 
             CASE it.[Type] 
                WHEN 'MAN' THEN 1 
                WHEN 'VROUW' THEN 2 
                WHEN 'KIND' THEN 3 
             END, 
             it.[BirthDate] ASC" />

In T-SQL this would be a perfecty normal way of sorting, but used in the EntityDataSource it throws the following exception:

The query syntax is not valid. Near identifier 'it', line 11, column 21.

How can I get this type of sorting to work in my EntityDataSource?

score 3 · Accepted Answer · edited Nov 28 '11 at 18:22

3

I was trying the very same thing today. I discovered on the Entity SQL Reference site that you apparently have to use CASE WHEN and cannot use CASE [value] WHEN. This approach, although not as it would be in T-SQL, did work for me. So your code should look like this:

<asp:EntityDataSource ID="myEntityDataSource" runat="server" 
ConnectionString="name=MySQLEntities1"      
DefaultContainerName="MySQLEntities1"      
EnableFlattening="False"      
EntitySetName="Persons"      
EntityTypeFilter="Persons"     
OrderBy="it.[Pack],               
CASE 
WHEN it.[Type] = 'MAN' THEN 1                  
WHEN it.[Type] = 'VROUW' THEN 2                  
WHEN it.[Type] = 'KIND' THEN 3 END, it.[BirthDate] ASC" />

MSDN Entity SQL Reference: 'CASE'

edited Nov 28 '11 at 18:22

Anne

26,765
9
65
71

answered Nov 28 '11 at 17:41

Homer Simpson

46
2

A-MA-ZING! You saved me a LOOOOOT of time! Muchos gracias!!! If I had some beer, you would get it! – Kees C. Bakker Nov 28 '11 at 20:07
1

@Kees C. Bakker : Great, my greetings that you've found the solution! It was interesting for me to why EF was not able handling `CASE` inside the `ORDER BY` – sll Nov 28 '11 at 20:26
How to use CASE in Select – Ivan Lewis Sep 21 '16 at 11:51

How to sort with a CASE statement in an EntityDataSource?

1 Answers1

Linked