This works on my machine:
ISQLFunction f = new SQLFunctionTemplate(NHibernateUtil.String,
"SUBSTRING(Name, CHARINDEX('@', Name) + 1, 0)");
var products = session.CreateCriteria(typeof(Product))
.AddOrder
(
Order.Asc
(
Projections.SqlFunction(f, NHibernateUtil.String)
)
)
.List();
I had to use CHARINDEX instead of LOCATE because I'm using SQL Server,
also, SUBSTRING requires a third parameter, I used 0.
RP
On Thursday, June 13, 2013 12:32:08 PM UTC+1, Milan Gornik wrote:
>
>
> Hello,
>
> I am using NHibernate version 2.0.5 and building ICriteria to display data
> from two joined tables. Criteria is sorted (AddOrder()) and also limited (
> SetFirstResult(), SetMaxResults()). Everything works out fine. Now, I am
> trying to perform ordering which should go by result of SQL functions
> performed on the attributes (which are already in the criteria). The SQL I
> would like to perform is as following:
>
> SELECT FromEmail FROM Emails
> ORDER BY SUBSTRING(FromEmail,LOCATE('@',FromEmail)+1);
>
> The SELECT part is more complex in my code but I'm keeping it minimal here
> for the sake of clarity. Can anyone help me with adding this? I was
> thinking that I should probably add new projection to my existing ones and
> that new projection should execute the SQL code SUBSTRING(...) and that
> then I should AddOrder() by that new property/alias. However, I'm having
> trouble forming the code that should do this. Or, if that is easier I could
> AddOrder() passing SqlFunction or SqlProjection. Any of the two
> approaches would be fine, if I could figure out the right syntax. Any help
> is appreciated!
>
> Milan Gornik
>
>
--
---
You received this message because you are subscribed to the Google Groups
"nhibernate-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
