< !-- somefunc() is a native SQL function -->
I I want to get all planets and order them by distance calculation attribute:
var planets = session
.CreateCriteria()
.AddOrder(Order. Asc("Distance"))
.List();
This is translated into the following query:
SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY somefunc()
Expected query:
SELECT Id as id0, somefunc() as formula0 FROM planets ORDER BY formula0
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< Planet>();
S QL:
SELECT somefunc() as formula0 FROM planets ORDER BY formula0
But it only populates the Distance attribute in the result, I really want to avoid manual All other properties projected to 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-selections, etc.), the important thing is to calculate the Distance property in the result object , And sort by this distance in SQL.
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
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 formula0But 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 formula0Edit, 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