.NET – Sort by formula field in NHibernate

Suppose I have the following mapping with formula attributes:






< !-- 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.

These two are exactly the same 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, 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 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

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 469 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.