Natural Sorting in NHibernate

Question

I am trying natural sort in my c# code using NHibernate.

I am using following code for sorting in NHibernate. "Code" is a nvarchar field for "Item" table.

public IQueryable<Item> SearchItems(string code)
        {
            var session = GetSession();
            var criteria = session.CreateCriteria(typeof(Item)).Add(Restrictions.InsensitiveLike("Code", code + "%"));
            var items = criteria.AddOrder(Order.Asc("Code")).List<Item>().AsQueryable();
            return items;
        }

Using above code I am getting output like

item1, 
item10, 
item100, 
item2, 
item20, ...

but i want

item1, 
item2, 
..., 
item10, 
..., 
item20, 
..., 
item100...

how I can replace it with natural sort?

On an unrelated note, I hope you know what `AsQueryable()` does. Normally it's useless/more than useless. — xanatos, Feb 27 '15 at 07:35

Radim Köhler · Accepted Answer · 2015-03-02T07:27:19.233

I would like to show you, how we can use NHibernate in these scenarios. I will use the SQL Server syntax, but the idea behind is working in any dialect.

SQL SERVER 2012

Firstly, let's agree, that this SQL Statement will give us the NUMBER part:

ISNULL(TRY_PARSE(SUBSTRING(Code, (PATINDEX('%[0-9]%', Code)), LEN(Code)) as int), 0)
   AS [NUM]

It does finds the char where is first number 0-9 and, takes it as a substring and call try_parse to get number (or 0 if ISNULL)

This could extract text part, and handle scenarios where the number is missing (e.g. value 'codeX' instead of 'code0')

LEFT(Code, IIF( PATINDEX('%[0-9]%', Code) > 0, PATINDEX('%[0-9]%', Code) - 1, 0))
   AS [TXT]

And now we can put that all togehter and with projections have this query, which is doing natural sort

// text part as projection
var textPart = Projections.SqlProjection(
    "LEFT(Code, IIF( PATINDEX('%[0-9]%', Code) > 0, PATINDEX('%[0-9]%', Code) - 1, 0)) AS [TXT]"
    , new string[] {}
    , new IType[] {}
    );

// number part as projection
var numberPart = Projections.SqlProjection(
    " ISNULL(TRY_PARSE(SUBSTRING(Code, (PATINDEX('%[0-9]%', Code)), LEN(Code)) as int), 0) AS [NUM]"
    , new string[] {}
    , new IType[] {}
    );
        ;

var criteria = session
      .CreateCriteria<Item>()
      .Add(Restrictions.InsensitiveLike("Code", code + "%"));

var items = criteria
      // here we order by our projectons
      .AddOrder(Order.Asc(textPart))
      .AddOrder(Order.Asc(numberPart))
      .List<Item>();

EXTEND:

There should be working life example

I created a script showing the solution described above. This script can be run against any MS SQL Server 2012 DB:

IF OBJECT_ID('[dbo].[MyTable]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MyTable]

CREATE TABLE [dbo].[MyTable] (
    [My_ID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](255) NOT NULL,
    CONSTRAINT [PK_My_ID] PRIMARY KEY CLUSTERED 
  (
    [My_ID] ASC
  )
)

INSERT INTO [dbo].[MyTable] ([Code])
VALUES ('Code1')
, ('Code2')
, ('Code10')
, ('Code20')
, ('Code100')
, ('Code200')

-- SELECT [Code] FROM [dbo].[MyTable] ORDER BY [Code]

SELECT [Code] FROM [dbo].[MyTable] ORDER BY
 LEFT(Code, IIF( PATINDEX('%[0-9]%', Code) > 0, PATINDEX('%[0-9]%', Code) - 1, 0))
 ,
 ISNULL(TRY_PARSE(SUBSTRING(Code, (PATINDEX('%[0-9]%', Code)), LEN(Code)) as int), 0)

Try to check it here

For Other DB engines (or preve version), just adjust the SELECT script, but idea how to use it with NHibernate should be clear

SQL Server 2008

There is a small draft of a version which could be used for sql server 2008

SELECT [Code] FROM [dbo].[MyTable] ORDER BY
 LEFT(Code, (CASE WHEN PATINDEX('%[0-9]%', Code) > 0 THEN PATINDEX('%[0-9]%', Code) - 1 ELSE 0 END) )
 ,
 ISNULL(CONVERT(decimal(18,3), (SUBSTRING(Code, (PATINDEX('%[0-9]%', Code)), LEN(Code)))), 0)

I created an example for you http://sqlfiddle.com/#!6/d41d8/24224, and extended the answer. Ther is version working for SQL server 2012 and draft for 2008. Good luck — Radim Köhler, Mar 02 '15 at 07:31
Thanks for the example. it is working in Sql server but this query not working as your c# code. I am using Sql server 2012. — Priya Mehta, Mar 03 '15 at 09:04
That's up to you. I showed you the way. I gave a proof on sqlfiddle. And it is working with NHibernate for me. Now it is your turn, to find what are you doing wrong. Start with SQL select, being sure that it is working.. next play with NHibernate. wish you luck — Radim Köhler, Mar 03 '15 at 09:06

score 1 · Answer 2 · edited May 23 '17 at 12:12

A) You can do it in SQL, but it gets quite complex (because it's quite complex to reference SQL functions from NHibernate, and/or it's quite complex/nearly impossible to use ROW_NUMBER() from NHiberante)

B) You can de-normalize your data in two separate additional columns in SQL. It's more complex to do when you write the data, but then sorting is "free": you have to create a CodePrefix (varchar) column and a CodeNumber (numeric nullable) column and save there the splitted value.

C) You are already materializing the query in your code snippet (your query is executed in the List<Item>()), and you are not using paging, so you can sort the items C#-side ("client side"). For this you can probably look at Natural Sort Order in C# . There are various solutions there, one of which is http://zootfroot.blogspot.it/2009/09/natural-sort-compare-with-linq-orderby.html

Natural Sorting in NHibernate

2 Answers2

SQL SERVER 2012

SQL Server 2008