In my app, I have a main class which is very large but links to most other data within the system. The search functionality that we are building basically allows each property and many of the relationships to other object or collections from this main object to be searched upon.

When investigating the differences between querying using the IQuery interface or ICriteria, the ICriteria interface provided a really nice, clean way of searching on all properties and collections within this object. This was great until we loaded a large amount of data into the db (900,000+ rows). Initially, I was only lazily loading collections, but not many-to-one associations and very quickly found out that the search returning a large result set was pretty much unuseable.

I then tried proxying the many-to-one associations which definitely made the generated sql query much smaller and faster, but the size of the objects reutrned was still much too large given each one contains 7 or 8 associated proxy objects.

The main problem is, when I do a search I only need 3 or 4 of the property values returned from the main object and none of the proxies. I know I can easily make this happen using an HQL query such as "select new ClassX(m.a, m.b) from Main m". The problem is that I lose all of the ICriteria funtionality which allows me to easily build a powerful search.

Is there any way that using ICriteria I can invoke functionality similar to the "select new ClassX()" style HQL and specify which specific columns I want returned? I do not want to filter using a transformer (which I think could be done) because the transformer methods are only called after the db query has been executed meaning that way more columns than I require have been returned from the db ... impacting performance.

On the other hand, is there a way that I can build my query using an ICriteria object and then extract the HQL or SQL from it without running List()? Then I could prepend this with my select new .... stuff.

If not, I will re-write using HQL, but it seems like such a waste of the ICriteria interface.