QueryOver API OrderBy使用案例 [英] QueryOver API OrderBy using Case

问题描述

如何使用QueryOver API执行以下LINQ to NHibernate查询.这将从数据库中获取所有Item记录的列表，并将状态为"Returned"的Items放置在列表的末尾.状态是一个Enum，它映射到数据库中的nvarchar.

How can I perform the following LINQ to NHibernate query using the QueryOver API. This gets a list of all records of Item from the DB and places Items with the status "Returned" to the end of the list. The status is an Enum which is mapped to a nvarchar in the database.

var workList = session.Query<Item>()
                .OrderBy(i=> i.Status == Status.Returned ? 1 : 0)
                .ToList();

SQL等效项是

SELECT *
FROM Item
ORDER BY case when Status='Returned' then 1 else 0 end

我当然尝试过

var workList = session.QueryOver<Item>()
                .OrderBy(i => i.Status == Status.Returned ? 1 : 0).Asc
                .ToList();

但是我得到了以下

InvalidOperationException:类型为'i'的变量 从范围``引用了'MyProject.Model.Entities.Item'，但这是 未定义

InvalidOperationException: variable 'i' of type 'MyProject.Model.Entities.Item' referenced from scope '', but it is not defined

由于这种情况下某些其他功能的问题，我无法使用LINQ.

I can't use LINQ because of an issue with some other functionality in this case.

推荐答案

在这里使用Projections.Conditional应该没问题，

You should be fine using Projections.Conditional here instead:

Item itemAlias = null;

var workList = 
    session.QueryOver<Item>(() => itemAlias)
        .OrderBy(Projections.Conditional(
            Restrictions.Where(() => itemAlias.Status == Status.Returned),
            Projections.Constant(1),
            Projections.Constant(0))).Asc
        .List();

这有点冗长，但是应该可以完成工作.

It's a little verbose but it should get the job done.

这篇关于QueryOver API OrderBy使用案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！