NHibernate formula field order

Question

Suppose I have the following mapping with the formula property:

 <class name="Planet" table="planets"> <id name="Id" column="id"> <generator class="native" /> </id> <!-- somefunc() is a native SQL function --> <property name="Distance" formula="somefunc()" /> </class> 

I would like to get all the planets and sort them by the calculated Distance property:

 var planets = session .CreateCriteria<Planet>() .AddOrder(Order.Asc("Distance")) .List<Planet>(); 

This translates to the following query:

 SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY somefunc() 

Desired request:

 SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY formula0 

If I set up a projection with an alias, it works fine:

 var planets = session .CreateCriteria<Planet>() .SetProjection(Projections.Alias(Projections.Property("Distance"), "dist")) .AddOrder(Order.Asc("dist")) .List<Planet>(); 

SQL:

 SELECT somefunc() as formula0 FROM planets ORDER BY formula0 

but it fills only the Distance property as a result, and I really like to avoid manually projecting over all the other properties of my object (there may be many other properties).

Is this possible with NHibernate? As a bonus, I would like to pass parameters to the native somefunc() SQL function. Everything that creates the desired SQL is acceptable (replacing the formula field with subselects, etc.). It is important to have the calculated Distance property in the resulting object and the order at that distance inside SQL.

Answer 1

These 2 are 100% identical in SQL. Why does it matter?

 SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY somefunc() SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY formula0 

Edit anyway:

The ORDER BY clause will simply repeat the SELECT function call, whether it is an alias or not

 SELECT Id as id0, somefunc(1, 'fish') as formula0 FROM planets ORDER BY somefunc(1, 'fish') SELECT Id as id0, somefunc(1, 'fish') as formula0 FROM planets ORDER BY formula0