如何使用在EntityDataSource CASE语句排序？ [英] How to sort with a CASE statement in an EntityDataSource?

问题描述

我使用的是 CASE 语句在我的EntityDataSource做自定义排序。请看下面的code：

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" />

在T-SQL，这将是排序的perfecty正常的方式，但在使用了 EntityDataSource 它抛出以下异常：

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

查询语法无效。近标识符'它'，11行，列
  21。

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

我怎样才能得到这个排序类型的工作我的 EntityDataSource ？

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

推荐答案

我今天想同样的事情。我发现实体SQL参考网站上，你显然需要使用 CASE WHEN 而不能使用 CASE [值当 。这种方法虽然不如它会在T-SQL，没有工作对我来说。所以，你的code应该是这样的：

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实体SQL参考：案例

这篇关于如何使用在EntityDataSource CASE语句排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！