CODEWITHSUNDEEP
c#sqlnhibernate
ask125342
ask125342

Reputation: 89

NHibernate: Create criteria from subquery

Have 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 do I create Criteria in NHiberbate? Language C#.

        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 entire table, and not one of those 10 elements.

Upvotes: 4

Views: 2454

Answers (1)

Andrew Whitaker
Andrew Whitaker

Reputation: 126052

Right now your query says Get me Sessions with StatusId of valueFailStatus as long as this subquery returns any results.

Since you're using Exists, you're not correlating the queries together to do what you want.

I think you're looking for Subqueries.PropertyIn instead. Something like this:

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

Upvotes: 5

Related Questions