How to get the value from a subquery in NHibernate?`

Question

I'm currently building a message board where I need to output the number of messages in a Thread.

• ID
• Name
• MessageCount

In plain SQL it would look like this but I can't find any documentation on how to make the inline select.

SELECT 
t.ID, 
t.Name 
(SELECT COUNT(*) FROM Messages m WHERE m.ThreadID = t.ID)
FROM Threads t 

I've only found examples on how to restrict the resultset using detached queries, not how to select the actual values from them.

How do i make an inline-select with NHibernate? I prefer using ICriteria over HQL.

EDIT: I simplified my over-complicated question to make it easier to understand.

Answer 1

You can use projections to do this. Using the QueryOver API in 3.X it would look something like this (subquery in your case will be different, but not too far off):

    var subquery = DetachedCriteria.For<ToppingUse> ("t")
            .Add (Restrictions.EqProperty ("t.Pizza.Id", "p.Id"))
            .SetProjection (Projections.Count ("Id"));

    Pizza p = null;

    var toppedPizzas = session.QueryOver<Pizza>(() => p)
        .Select(Projections.Property(() => p.Id)
            , Projections.Property(() => p.Sauce)
            , Projections.Property(() => p.Crust)
            , Projections.Alias(Projections.SubQuery(subquery), "ToppingCount"))
        .List<object[]>(); //then you need to handle mapping on your own somehow (look into ResultTransformers if needed)

this translates to the following criteria:

    var subquery = DetachedCriteria.For<ToppingUse> ("t")
            .Add (Restrictions.EqProperty ("t.Pizza.Id", "p.Id"))
            .SetProjection (Projections.Count ("Id"));

    var toppedPizzas = session.CreateCriteria<Pizza>("p")
        .SetProjection(Projections.Property("p.Id")
            , Projections.Property("p.Sauce")
            , Projections.Property("p.Crust")
            , Projections.Alias(Projections.SubQuery(subquery), "ToppingCount"))
        .List<object[]>();//then you need to handle mapping on your own somehow (look into ResultTransformers if needed)

You just need to make sure you use the same aliases within your subquery and outer query.