NHibernate, enter rowcount if the criteria have a group

Question

I need to get the number of rows from the query criteria and the criteria received by the projection team. (paging needs to work)

eg.

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

I need to avoid CreateSQL since I have many criteria .. and limitations, etc. complicated.

Can you make a sub-criterion (detached) and then select count(*) from .. ? I can not understand how?

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

Answer 1

I think this can be done using NH Multi Queries.

Here is some information about this: http://ayende.com/blog/3979/nhibernate-futures An example shows how we can run a query and get the results of this query in one reverse direction to the database.

And here is a good answer that looks like what you want to achieve: nhibernate 2.0 Effective data control DataList Control and ObjectDataSource , in which they get the results page AND the total number of records in one reverse direction to the database.

In addition, I doubt that you can read the pure @@rowcount value using NH without changing the sql query, since @@rowcount is database specific.

My assumption would be that for your case, GetSql cannot be avoided from the criteria unless you simplify your query or approach. It might be worth a try.

If you can place more snippets of your code, maybe someone can figure it out.

Answer 2

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

 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[]>(); 

Answer 3

Just think a little outside the window and remove the query complexity from NHiberate. You can make a view for the query in the database, and then create a mapping for the view.

Answer 4

I solved this problem in java version (Hibernate). The problem is that the RowProjection function looks something like this:

 count(*) 

This is an aggregated function: therefore, if you create the "group by" property, your result is a list of a grouped row, and for each row you have a total number of groups.

For me, with the oracle database, to make it work, I created a custom projection that, instead of creating a count (*) function, has a function

 count(count(*)) 

and the property in the group by clause is not written to the select ... from command. This is not so simple for this, the problem is that you need to provide the entire stack to create the correct sql, so with the java version I have to subclass class 2: SimpleProjection ProjectionList

After that, my request is generated as:

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

to become

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

and the result is the common string given

 select col1, col2 from table1 group by col1, col2 

(used with pagination system)

I post a java version of the classes here if they are useful to 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; } } 

I hope for this help.