Ordering by formula fields in NHibernate

Question

Suppose that I have the following mapping with a 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 planets and order them by the Distance calculated property:

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

This is translated to the following query:

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

Desired query:

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

If I set 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 populates only the Distance property in the result and I really like to avoid projecting manually over all the other properties of my object (there could be many other properties).

Is this achievable with NHibernate? As a bonus I would like to pass parameters to the native somefunc() SQL function. Anything producing the desired SQL is acceptable (replacing the formula field with subselects, etc...), the important thing is to have the calculated Distance property in the resulting object and order by this 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, still the same:

The ORDER BY clause will simply repeat the SELECT func call whether aliased 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