NHibernate: Creating Criteria from a Subquery

Question

Has SQL:

select COUNT(1) from
(SELECT TOP 10 Status
FROM SESSIONS
where SERVER_ID = 2569
ORDER by ID desc) as s
where s.STATUS = 32768

How to create criteria in NHiberbate? C # language.

        var subQuery = DetachedCriteria.For(typeof(Session))
            .SetProjection(Property.ForName("Status"))
            .Add(Restrictions.Eq("Server", server))
            .AddOrder(Order.Desc("Id"))
            .SetMaxResults(10);
        var query = DetachedCriteria.For(typeof(Session))
            .Add(Subqueries.Exists(subQuery))
            .Add(Restrictions.Eq("Status.Id", valueFailStatus))
            .SetProjection(Projections.RowCount());
        using (var session = NHibernateHelper.OpenSession())
            return (int)query.GetExecutableCriteria(session)
            .UniqueResult();

But I get a sample of the whole table, not one of these 10 elements.

Answer 1

Now your query tells Get me Sessionwith StatusIdfrom valueFailStatus, until this subquery returns any results.

As you use Exists, you do not match queries together to do what you want.

I think what you are looking for Subqueries.PropertyIn. Something like that:

var subQuery = DetachedCriteria.For(typeof(Session))
    .SetProjection(Property.ForName("Id"))
    .Add(Restrictions.Eq("Server", server))
    .AddOrder(Order.Desc("Id"))
    .SetMaxResults(10);

var query = DetachedCriteria.For(typeof(Session))
    .Add(Subqueries.PropertyIn("Id", subQuery))
    .Add(Restrictions.Eq("Status", 32768))
    .SetProjection(Projections.RowCount());

var result = (int)query.GetExecutableCriteria(s)
    .UniqueResult();

... which will generate SQL that looks like this:

SELECT
    count(*) as y0_ 
FROM
    Sessions this_ 
WHERE
    this_.Id in (
        SELECT
            TOP (10) this_0_.Id as y0_ 
        FROM
            Sessions this_0_ 
        WHERE
            this_0_.Server_Id = 2569
        ORDER BY
            this_0_.Id desc
    ) 
    and this_.Status = 32768