How to create a Projection subquery, give it an alias, and sort by alias in NHibernate using the criteria API

Question

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

one of the posters gives the answer:

You need to create Projection (...), give it an alias, and then you can sort it by alias. There is no time to post details, but I'm sure it will work.

Can someone provide a simple example, using the criteria API, of a query that Projection uses to create a subquery , then uses that subquery as an alias and then orders this alias?

Hooray!

Answer 1

You can add more DetachedCriteria based on your needs.

Here is my example:

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();

.

Answer 2

:

DetachedCriteria sum = DetachedCriteria.For(typeof(MasterAsset), "asset2")
                  .SetProjection(Projections.Sum("PhysicCondition"));
Session.CreateCriteria(typeof(MasterAsset), "asset1")
          .SetProjection(Projections.ProjectionList().Add(Projections.Property("IDMasterAsset"), "IDAsset"))
          .Add(Subqueries.PropertyLt("PhysicCondition", sum))
          .AddOrder(Order.Asc("IDAsset"))
          .List();