Is there a way to create a property filter sort with Nhibernate QueryOver

Question

I have scenario where I need to conduct the sorting on a property (User.Name) in the NHibernate QueryOver query where the property contains a string that I need to grab the last portion of a name and order ASC. If I was doing it on the returned results, it might be something like:

.....
var query = session.QueryOver<User>().....
.....
query.OrderBy(u => SortByLastName(u.Name));

private string SortByLastName(string name)
    {
        if (string.IsNullOrEmpty(name)) {
            name = " ";
        }

        var nameArray = name.Trim().Split(' ');
        var lastName = nameArray[nameArray.Length - 1];

        return lastName.ToLower();
    }

In SQL it would be a combination of the SUBSTRING, CHARINDEX (and possibly other functions) to grab the last portion of a name (ie. Joe Smith, Jane A. Doe) and sort on the last name.

Question

Is there a way in NHibernate QueryOver to set this up where you didn't have to roll it up in a Stored Proc which is called by the NHibernate or passing in the Raw SQL query through .CreateSQlQuery(sql)?

But instead build this logic directly into the QueryOver?

score 3 · Accepted Answer · edited May 23 '17 at 12:05

3

You can do this by creating Projections that call SQL functions. In your case there are nested functions, so there will be multiple building steps.

Here is a draft solution. I haven't tested it, but it gives the idea of the approach:

IProjection nameField = Projections.Property<User>(u => u.Name);
IProjection charIndex = Projections.SqlFunction("CHARINDEX",
                                                NHibernateUtil.String,
                                                nameField,
                                                Projections.Constant(" ", NHibernateUtil.String)
                                               );
IProjection subString = Projections.SqlFunction("SUBSTRING",
                                                nHibernateUtil.String,
                                                nameField,
                                                charIndex,
                                                Projections.Constant(999, NHibernateUtil.UInt32);



var query = session.QueryOver<User>().OrderBy(subString).Asc;

Unfortunately, this solution would not work if there are spaces in the first name. (Jane A. Doe). I'm not aware of an SQL function to find the last space in a string.

See this discussion: Find index of last occurrence of a sub-string using T-SQL

edited May 23 '17 at 12:05

Community

1
1

answered Aug 12 '13 at 23:38

Andrew Shepherd

40,674
26
128
192

Thanks, but I am getting a "Current dialect NHibernate.Dialect.MsSql2008Dialect doesn't support the function: CHARINDEX" error message when attempting to ues the CHARINDEX function. – pghtech Aug 13 '13 at 11:05
I got this work, the functions needed to be registered with Nhibernate RegisterFunction(). But this was what I had to do thanks @Andrew Shepherd. – pghtech Aug 13 '13 at 16:11
2

I got passed this last error by registering the missing functions with Nhibernate using the RegisterFunction() menthods in my own derived MsSQL2008 dialect class. – pghtech Aug 13 '13 at 16:18
That's really interesting, I didn't know you had to do that. It's intriguing as to why nHibernate forces you to register functions beforehand. I suspect it comes from the requirements of parsing the HQL language, the parser would need to know all of the function names upfront. – Andrew Shepherd Aug 13 '13 at 22:15

Is there a way to create a property filter sort with Nhibernate QueryOver

1 Answers1