How do I convert following SQL to NH Queryover,

select COUNT(*)
(select p.CODE,sl.BATCH from STORELOCATION sl
right join PRODUCT p on p.CODE = sl.CODE
group by p.CODE,sl.BATCHID)
as t

asked Aug 24 ’11 at 6:42

What have you tried so far? – Rippo Aug 24 ’11 at 7:51
Actually this is more simplified version of the query I want, the basic idea is to get the row count of a query with multiple groupings to use it for paging. I have no idea how to get the inner query part to work, that is why this question is added. – Low Flying Pelican Aug 24 ’11 at 8:10

2 Answers


I’ve tried to solve it using QueryOver and Alias; sorry, but I can’t test this code now. 🙁

        ProductModel myProd = null;
        StoreLocationModel myLocation = null;

        var qOver = _HibSession.QueryOver<ProductModel>(() => myProd)
            .JoinAlias(() => myProd.Locations, () => myLocation, JoinType.LeftOuterJoin)
            .Select(Projections.GroupProperty(myProd.CODE), Projections.GroupProperty(myLocation.BATCHID))

I hope it’s helpful!

answered Aug 24 ’11 at 8:15


Thank you for the answer, The SQL generated by this does not have the Group By section, It give the total row count which is different from the no of rows returned with group by clause. – Low Flying Pelican Aug 24 ’11 at 8:27
@Adipa Gunasekara: Ok, try to use .Select(Projections.Group(() => myProd.CODE), Projections.Group(() => myLocation.BATCHID)). I’m sorry but I can’t try it again 🙁 – Faber Aug 24 ’11 at 8:31
It also gives the same results without group by section, I have tried this with quite a lot of different combinations but as soon as I try to get the rowCount the group by clause is goes missing in the query, so I believe only way to do this is to execute query as inner query as I have suggested in the question, still I do not have clear idea if it’s even possible with NH Queryover. – Low Flying Pelican Aug 24 ’11 at 8:44

up vote
down vote


I have managed to achieve this using a custom projection, if anyone interested code is as follows,

    public class GroupCountProjection : SimpleProjection
        private PropertyProjection[] _projections;

        public GroupCountProjection(PropertyProjection[] projections)
            _projections = projections;

        public override bool IsAggregate
            get { return true; }

        public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
            return new IType[] { NHibernateUtil.Int32 };

        public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
            SqlStringBuilder result = new SqlStringBuilder()
                .Add(" count(*) as y")
                .Add("_ from ( select ");
            for (int index = 0; index < _projections.Length; index++)
                PropertyProjection projection = _projections[index];
                if (index > 0)
                result.Add(projection.ToSqlString(criteria, ++position, criteriaQuery, enabledFilters));
            result.Add(" ");
            return result.ToSqlString();

        public override string ToString()
            return "select count(*)";

        public override bool IsGrouped
            get { return true; }

        public override SqlString ToGroupSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
                                                   IDictionary<string, IFilter> enabledFilters)
            SqlStringBuilder result = new SqlStringBuilder();
            for (int index = 0; index < _projections.Length; index++)
                PropertyProjection projection = _projections[index];
                if (index > 0)
                result.Add(StringHelper.RemoveAsAliasesFromSql(projection.ToSqlString(criteria, 0, criteriaQuery,enabledFilters)));
            result.Add(") as tbly");
            return result.ToSqlString();

here, the constructor of the projection needs to be passed with all the group by projections like,

var countQuery = GetProductQuery(); // this is the queryover 
                .Select(new GroupCountProjection(new[]{
                    Projections.Group(() => _productAlias.Code),
                    Projections.Group(() => _storeLocationAlias.Batch),
int resultCount = (int)countQuery.List<object>().SingleOrDefault();

answered Aug 25 ’11 at 3:50

Your Answer


Sign up or login

Sign up using Google

Sign up using Facebook

Sign up using Stack Exchange

Post as a guest



By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you’re looking for?
Browse other questions tagged
or ask your own question.