Geek asks and answers

Nontrivial order in QueryOver query

Ive got my query built with QueryOver api ... now the problem is that I need to sort it by some special value that needs to be calculated in flight ...

I have a table of my products and I need to sort it by the distance from some specific coordinates .. the equivalent in SQL will look like ORDER BY power(abs(p.x - @x),2) + power(abs(p.y - @y),2)

Now I have no idea how to write it in a QueryOver query .. any suggestions?

I will be happy for any help!

+3
source share
1 answer

Using QueryOver is a bit of a problem because we need to use Projections when using SQL functions.

I dropped abs(...)because it is not necessary becausex * x == -x * -x

1: SqlProjection ( , )

int x = 10;
int y = 20;

var result = session.QueryOver<Product>()
    .OrderBy(Projections.SqlProjection(
        @"power(x - " + x.ToString() + ", 2) + power(y - " + y.ToString() + ", 2) as tmporder",
        new string[] { "test" }, 
        new NHibernate.Type.IType[] { NHibernateUtil.Int32 })).Asc
    .List();

2: SqlFunction - :

- ( OperatorProjection , , )

.OrderBy(Projections.SqlFunction("power", NHibernateUtil.Double,
    new ArithmeticOperatorProjection("+", NHibernateUtil.Int32,
        Projections.Property<Product>(p => p.x), Projections.Constant(x)),
    Projections.Constant(2))).Asc

NHibernate OperatorProjection Part 1

+4

More articles:


All Articles