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

i have scenario need conduct sorting on property (user.name) in nhibernate queryover query property contains string need grab last portion of name , order asc. if doing on returned results, might 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 combination of substring, charindex (and possibly other functions) grab last portion of name (ie. joe smith, jane a. doe) , sort on last name.

question

is there way in nhibernate queryover set didn't have roll in stored proc called nhibernate or passing in raw sql query through .createsqlquery(sql)?

but instead build logic directly queryover?

you can creating projections call sql functions. in case there nested functions, there multiple building steps.

here draft solution. haven't tested it, gives idea of 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, solution not work if there spaces in first name. (jane a. doe). i'm not aware of sql function find last space in string.

see discussion: find index of last occurrence of sub-string using t-sql