Complex QueryOver

Hi I have to translate folowing sql to QueryOver Will it be possible ? my actual query may more complex. But I have stuck in this stage.

SELECT InnerQuery.USERID,
InnerQuery.TRAFFICZONEID,
InnerQuery.StatusCategory,
COUNT(*) AS LineCount
FROM (
SELECT MissionID,
    UserId,
    TRAFFICZONEID,
    CASE 
        WHEN status BETWEEN 1
                AND 5
            THEN 1
        WHEN status BETWEEN 6
                AND 8
            THEN 2
        WHEN status BETWEEN 9
                AND 17
            THEN 3
        ELSE 0
        END AS [StatusCategory]
FROM mission
) AS InnerQuery
LEFT OUTER JOIN trafficzone t ON InnerQuery.TRAFFICZONEID = t.Trafficzoneid

GROUP BY InnerQuery.USERID,
InnerQuery.TRAFFICZONEID,
InnerQuery.StatusCategory

Is开发者_开发技巧 it possible to do that kind of uery in QueryOver ? Or else what is the best way to do it with NHibernate ?

Thank you,

DineshNS

I suggest you use HQL for this.

From all the query methods supported by NHibernate, it's the one that works best with free-form queries.

The only thing it does NOT support directly is an outer join to a subquery. You can fake that using an implicit join with the subquery (from A, (subquery) B where A.X = B.X) and a UNION for items that don't have matching elements in the subquery.

For a complex query, you could create a view in database and use that from NHibernate.