How to create a subquery Projection, give it an Alias, and sort by the Alias in NHibernate with the Criteria API

Question

forum.hibernate.org/viewtopic.php?p=2378849

one of the posters gives this answer:

You need to create a Projection (...), give it an alias and you can then sort by the alias. No time to post the details but I'm pretty sure that would work.

Could someone provide a simple example, using the Criteria API, of a query that uses a Projection to do a subquery, and then uses that subquery as an Alias, and then orders by that Alias?

cheers!

Answer 1

you can add more DetachedCriteria based on your needs.

Here is my sample :

DetachedCriteria sum = DetachedCriteria.For(typeof(MasterAsset), "asset2")
                    .SetProjection(Projections.Sum("PhysicCondition"));

DetachedCriteria count = DetachedCriteria.For(typeof(MasterAsset), "asset3")
                    .SetProjection(Projections.Count("PhysicCondition"));

Session.CreateCriteria(typeof(MasterAsset), "asset1")
                    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Property("IDMasterAsset"), "IDAsset"))
                    .Add(Subqueries.PropertyLt("PhysicCondition", sum))
                    .Add(Subqueries.PropertyLe("PhysicCondition", count))
                    .AddOrder(Order.Asc("IDAsset"))
                    .List();

Hope this help.