Ordering by formula fields in NHibernate
- by Darin Dimitrov
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.
