.NET – Sort by formula field in NHibernate

 
 
 

 < !-- somefunc() is a native SQL function -->
 

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

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

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

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

SELECT somefunc() as formula0 FROM planets ORDER BY formula0

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

Suppose I have the following mapping with formula attributes:

 
 
 

 
 
< /pre> 
I want to get all planets by distance calculation properties and order them:
 
 
var planets = session
 .CreateCriteria()
 .AddOrder(Order.Asc("Distance"))
 .List();
This is translated into the following query:

SELECT Id as id0, somefunc() a s formula0 FROM planets ORDER BY somefunc()
Expected query:

SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY formula0< /pre> 
If I use an alias to set the projection, it works fine:
 
 
var planets = session
 .CreateCriteria()
 .SetProjection(Projections.Alias(Projections.Property("Distance"), "dist"))
 .AddOrder(Order.Asc("dist"))
 .List(); 
SQL:

SELECT somefunc() as formula0 FROM planets ORDER BY formula0
But it only populates the Distance attribute in the result , I really want to avoid manually projecting to all other properties of my object (and possibly many other properties).
Can this be achieved with NHibernate? As a bonus, I want to pass parameters to the native somefunc() SQL function. Anything that produces the required SQL is acceptable (replace formula fields with sub-selects, etc.), the important thing is to calculate the Distance property in the result object , And sort by this distance in SQL.

These two are exactly the same in SQL. Why does this 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, regardless of whether there is an alias.

 < pre>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