NHibernate中不区分大小写的排序

Consider the following criteria query:

请考虑以下条件查询:

var x = SomeCriteria.AddOrder(new Order("Name", true)).List();

var x = SomeCriteria.AddOrder(new Order(“Name”,true))。List();

This will order the result set by the Name property, but case sensitive:

这将按名称属性对结果集进行排序,但区分大小写:

"A1"
"B1"
"a2"

Any ideas how to add the order case insensitive so result "a2" will end up before "B1"?

任何想法如何添加订单不区分大小写结果“a2”将在“B1”之前结束?

4 个解决方案

#1

You should be able to accomplish this by ordering on a projection that normalizes the case for you. For example, Oracle has a "lower" function that will lower case string data types like varchar2 and nvarchar2; so I will use this sql function to form a projection that will order appropriately.

您应该能够通过订购一个为您规范化案例的投影来实现这一目标。例如,Oracle有一个“较低”的函数,它将降低案例字符串数据类型,如varchar2和nvarchar2;所以我将使用这个sql函数来形成一个适当排序的投影。

var projection = Projections.SqlFunction("lower", 
                                         NHibernateUtil.String, 
                                         Projections.Property("Name"));

var x = SomeCriteria.AddOrder(Orders.Asc(projection)).List()

If you're using SQL Server, I'd recommend using the "upper" function instead of "lower" for efficiency. Microsoft has optimized its native code for performing uppercase comparisons, where the rest of the world seems to have optimized on lowercase.

如果您使用的是SQL Server,我建议使用“upper”函数而不是“lower”来提高效率。 Microsoft已优化其本机代码以执行大写比较,其中世界其他地方似乎已针对小写进行了优化。

#2

Hibernate (Java) has an "ignoreCase()" method on the "Order" class, but it looks like NHibernate does not have this method on its "Order."

Hibernate(Java)在“Order”类上有一个“ignoreCase()”方法,但看起来NHibernate在其“Order”上没有这个方法。

This is how I was thinking you could do it:

这就是我认为你可以这样做的方式:

var x = SomeCriteria.AddOrder(new Order("Name", true).IgnoreCase()).List();

But unfortunately, there is no IgnoreCase().

但不幸的是,没有IgnoreCase()。

As a workaround, you could use an HQL or SQL query - either of those should allow you to order case-insensitive.

作为一种变通方法,您可以使用HQL或SQL查询 - 其中任何一个都应该允许您按顺序排列不区分大小写。

#3

This probably depends on a case-sensitivity setting on your database server. I suspect that NHibernate just issues an "ORDER BY" clause; at least, I can't imagine what else it would do. For SQL Server, the default sort order (collation) is dictionary order, case insensitive.

这可能取决于数据库服务器上的区分大小写设置。我怀疑NHibernate只发出一个“ORDER BY”子句;至少,我无法想象它会做什么。对于SQL Server,默认排序顺序(排序规则)是字典顺序,不区分大小写。

This article gives some techniques for performing case sensitive searches in SQL Server. However, my advice is to sort the list that is returned by the query in code. That solution preserves the database independence of NHibernate and let's you customize the sort order per your needs.

本文提供了一些在SQL Server中执行区分大小写搜索的技术。但是,我的建议是在代码中对查询返回的列表进行排序。该解决方案保留了NHibernate的数据库独立性,让您根据需要自定义排序顺序。

#4

As I know the responses to my query are always fairly small, I ended up querying the data as normal and sorting them afterwards using Linq. It works, so why bother tweaking NHibernate ;) (Using SQLite, btw)

据我所知,对我的查询的响应总是很小,我最终查询数据正常,然后使用Linq对它们进行排序。它有效,所以为什么还要调整NHibernate;)(使用SQLite,顺便说一句)

#1

var projection = Projections.SqlFunction("lower", 
                                         NHibernateUtil.String, 
                                         Projections.Property("Name"));

var x = SomeCriteria.AddOrder(Orders.Asc(projection)).List()

#2