NHibernate, get rowcount when criteria have a group by

Question

I need to get the count of rows from a criteria query and the criteria got group by projections. (needed to get paging to work)

E.g.

projectionList.Add(Projections.GroupProperty("Col1"), "col1")
              .Add(Projections.CountDistinct("Col2"), "Count");

I need to avoid CreateSQL, since I have a lot of criteria.. and the restrictions etc are complex.

Can you do a subcriteria (detached) and then select count(*) from .. ? Can't figure out how?

EDIT: I solved it by getting the sql from the criteria and then modifying it so that it now works! GetSql from criteria

Dupree

I'd like to do the same thing ... but avoid the GetSql solution. – Extravascular

Yes, this solution was painful, needed to get the sql from the criteria but you can't get the parameter types or values from the criteria (in a easy way) so we needed another collection to store the values, and they need to be in correct order etc.. – Intimist

What did you actually put into the SQL? I still don't understand what result you need. – Preshrunk

Erik, could you please show a bit bigger chunk of your query code? – Dulciana

I should say that we have now switched to Dapper.NET for these kinds of queries! – Intimist

Answer 1

I think this can be done by using NH Multi Queries.

Here is some stuff about it: http://ayende.com/blog/3979/nhibernate-futures Example shows how we can run query and get results count of that query in one roundtrip to the database.

And here is good answer, which sounds similar to what you want to achieve: nhibernate 2.0 Efficient Data Paging DataList Control and ObjectDataSource in which they get the result page AND total records count in one roundtrip to the database.

Also, I doubt that it is possible to read pure @@rowcount value with NH without changing sql query, as @@rowcount is database specific thing.

My assumption would be that for your case it is not possible to avoid GetSql from criteria solution, unless you simplify your query or approach. Maybe it worth to try this as well.

If you can post bigger chunk of your code, probably someone will be able to figure this out.

Dulciana

This only works for non-complex queries, the problem is that i got queries created with criteria that got group by projections. When you use any of the helpers, or do like all the examples on the net, the sql query is just wrong.. – Intimist

Answer 2

Not entirely sure what you want, but something like this should work (if I understand your question properly):

var subQuery = DetachedCriteria.For<SomeClass>()
   .Where(... add your conditions here ...);

var count = Session.CreateCriteria<SomeClass>()
   .Where(Property.ForName("Col1").In(
      CriteriaTransformer.Clone(subQuery).SetProjection(Projections.Property("Col1"))
   .SetProjection(Projections.Count())
   .FutureValue<int>();

var results = subQuery.GetExecutableCriteria(Session)
            .SetProjection(Projections.GroupProperty("Col1"), "col1"),
                           Projections.CountDistinct("Col2"), "Count")
            ).List<object[]>();

Mikey

It is something I proposed as well, but see comment below my answer. – Dulciana

Answer 3

Just to think a bit outside the box and remove the query complexity from NHiberate. You can make a View for the query in the database and then make a mapping for the view.

Xerophilous

Answer 4

I 've resolved this problem on the java version (Hibernate). The problem is that the RowProjection function is some like:

count(*) 

That is an aggregate function: so if you create a 'group by' property your result is a list of the grouped row and for each row you have the total count of the group.

For me, with oracle database, to make it work i've create a custom projection that, instead of create function count(*), the function is

count(count(*)) 

and the property in the group by clause are not written in the select ... from statement. To do that it not that simple, the problem is that you have to provide all stack to create the right sql so, with the java version I've to subclasse 2 class: SimpleProjection ProjectionList

After that my query generated as:

select count(*), col1, col2 from table1 group by col1, col2

become

select count(count(*)) from table1 group by col1, col2

and the result are the total row given by

select col1, col2 from table1 group by col1, col2

(usable with pagination system)

I post here the java version of the classes, if are useful for you:

public class CustomProjectionList extends ProjectionList {

    private static final long serialVersionUID = 5762155180392132152L;


    @Override
    public ProjectionList create() {
        return new CustomProjectionList();
    }

    public static ProjectionList getNewCustomProjectionList() {
        return new CustomProjectionList();
    }

    @Override
    public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery) throws HibernateException {
        StringBuffer buf = new StringBuffer();
        for (int i = 0; i < getLength(); i++) {
            Projection proj = getProjection(i);
            String sqlString = proj.toSqlString(criteria, loc, criteriaQuery);
            buf.append(sqlString);
            loc += getColumnAliases(loc, criteria, criteriaQuery, proj).length;
            if (i < (getLength() - 1) && sqlString != null && sqlString.length() > 0)
                buf.append(", ");
        }
        return buf.toString();
    }

    private static String[] getColumnAliases(int loc, Criteria criteria, CriteriaQuery criteriaQuery, Projection projection) {
        return projection instanceof EnhancedProjection ?
                ( ( EnhancedProjection ) projection ).getColumnAliases( loc, criteria, criteriaQuery ) :
                projection.getColumnAliases( loc );
    }


}

public class CustomPropertyProjection extends SimpleProjection {


    private static final long serialVersionUID = -5206671448535977079L;

    private String propertyName;
    private boolean grouped;


    @Override
    public String[] getColumnAliases(int loc, Criteria criteria, CriteriaQuery criteriaQuery) {
        return new String[0];
    }

    @Override
    public String[] getColumnAliases(int loc) {
        return new String[0];
    }

    @Override
    public int getColumnCount(Criteria criteria, CriteriaQuery criteriaQuery) {
        return 0;
    }


    @Override
    public String[] getAliases() {
        return new String[0];
    }

    public CustomPropertyProjection(String prop, boolean grouped) {
        this.propertyName = prop;
        this.grouped = grouped;
    }

    protected CustomPropertyProjection(String prop) {
        this(prop, false);
    }

    public String getPropertyName() {
        return propertyName;
    }

    public String toString() {
        return propertyName;
    }

    public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) 
    throws HibernateException {
        return new Type[0];
    }

    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
    throws HibernateException {

        return "";
    }

    public boolean isGrouped() {
        return grouped;
    }

    public String toGroupSqlString(Criteria criteria, CriteriaQuery criteriaQuery) 
    throws HibernateException {
        if (!grouped) {
            return super.toGroupSqlString(criteria, criteriaQuery);
        }
        else {
            return StringHelper.join( ", ", criteriaQuery.getColumns( propertyName, criteria ) );
        }
    }

}

public class CustomRowCountProjection extends SimpleProjection {
    /**
     * 
     */
    private static final long serialVersionUID = -7886296860233977609L;


    @SuppressWarnings("rawtypes")
    private static List ARGS = java.util.Collections.singletonList( "*" );

    public CustomRowCountProjection() {
        super();
    }


    public String toString() {
        return "count(count(*))";
    }

    public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return new Type[] {
                getFunction( criteriaQuery ).getReturnType( null, criteriaQuery.getFactory() )
        };
    }

    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        SQLFunction countSql = getFunction( criteriaQuery );
        String sqlString = countSql.toString() + "(" +  countSql.render( null, ARGS, criteriaQuery.getFactory() ) + ") as y" + position + '_';
        return sqlString;
    }

    protected SQLFunction getFunction(CriteriaQuery criteriaQuery) {
        SQLFunction function = criteriaQuery.getFactory()
                .getSqlFunctionRegistry()
                .findSQLFunction( "count" );
        if ( function == null ) {
            throw new HibernateException( "Unable to locate count function mapping" );
        }
        return function;
    }
}

Hope this help.

Juanajuanita