如何在 EntityDataSource 中使用 CASE 语句进行排序？

发布于 2024-12-18 04:33:44 字数 746 浏览 2 评论 0原文

我在 EntityDataSource 中使用 CASE 语句来进行自定义排序。考虑以下代码：

<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 中，这将是一种完美的正常排序方式，但在 EntityDataSource 中使用时会引发以下异常：

查询语法无效。靠近标识符“it”，第 11 行，列 21.

如何在我的 EntityDataSource 中使用这种类型的排序？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

七禾 2024-12-25 04:33:44

今天我也在尝试同样的事情。我在 Entity SQL Reference 站点上发现您显然必须使用 CASE WHEN 且不能使用 CASE [value] WHEN 。这种方法虽然不像 T-SQL 中那样，但对我来说确实有效。因此，您的代码应如下所示：

<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 参考：'CASE'

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'

回复收藏 0 原文

~没有更多了~