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!

score 3 · Accepted Answer · answered Jan 07 '11 at 06:58

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.

Hi dany, I found an example that might give you an inspiration. check this out : http://davybrion.com/blog/2008/10/querying-with-nhibernate/ — BantenCity, Jan 07 '11 at 07:12

BantenCity · Answer 2 · 2011-01-07T05:55:14.433

1

Here is my simple sample :

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

edited Jan 07 '11 at 05:55

answered Jan 07 '11 at 05:07

BantenCity

308
1
2
6

hey, thanks bantencity, though I wanted an example using a subquery, not just sum. any ideas? I've edited my question to make it clearer. thanks again – andy Jan 07 '11 at 05:28
thanks bantencity, actually your answer is good, my question is bad! I'll try asking a different question – andy Jan 07 '11 at 05:32

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

2 Answers2