NHibernate - Only retrieve specific columns when using Critera queries?

Question

I'm propositioning NHibernate for a project we're doing at my company and I'd like to know if NHibernate can be optimized to only retrieve specific columns on a table when using the Criteria query language.

For example. Let's say I have a table with 30 columns on it and this is mapped to an object using NHibernate that is a 1-for-1 match against the table. However, for a particular function of the system I only care about two of those columns.

Now, I know I can use HQL and do a CreateQuery that will accomplish this but that requires I create a constructor for each combination of fields I'd like to selectively retrieve. This could be a huge pain from a maintenance standpoint since I won't catch missing constructors till runtime.

I like the Criteria query language since it produces parametrized SQL instead of straight SQL queries from HQL. I see there is an "Exclude" model for not including certain columns but in most cases I will include more columns than exclude.

UPDATE:

Thanks to the comment below I looked into projections and this still isn't quite the ideal situation for me. When using the following:

var list = session .CreateCriteria(typeof (Task)) .SetProjection(Projections.ProjectionList().Add(Projections.Property("Id"))) .List();

I end up with the variable "list" just being ints, I'd prefer to have my full Task object but with all the fields set to their default values. Is this even possible? Everything I see so far says no.

Thanks!

Answer 1

Yes you can do this with criteria queries by using projections. Simply project only the properties you wish to use and only those will be included in the select clause of the compiled query.

http://nhforge.org/doc/nh/en/index.html#querycriteria-projection

Update to edit

There are several ways to accomplish this, with some limitations however. 1) The NHibernate way.

var list = session.CreateCriteria(typeof (Task))
.SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("Name"), "Name")
                   .Add(Projections.Property("ID"), "ID")
)
.SetResultTransformer(Transformers.AliasToBean(typeof (Task)))
.List();

Simply assign the property name as an alias to your projection and the AliasToBean transformer will map those projections to an actual class. The limitation to this method is that any properties that you map must have a setter in the POCO class, this can be a protected setter but it must have a setter.

You can also do this with linq as well in an a slightly different fashion

var list = session.CreateCriteria(typeof (Task))
.SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("Name"))
                   .Add(Projections.Property("ID"))
)
.List<IList>()
.Select(l => new Task() {Name = (string) l[0], ID = (Guid) l[1]});

This is simply using linq to map the indexed list that is ouput into a new instance of the Task class. The same limitation as above applies except that this is a bit more severe in that all the properties mapped must have a public setter because that is what linq uses to do fill in the object.

I hope this helps you.

Answer 2

I'm not sure if this will suit your purposes but this is only a suggestion: if the query ends up to be something that you always use anyway, you can create an SQL View for it, then create a mapping file against the View.

NHibernate will treat the View just like it would any table, although of course CRUD operations would be a problem vis-a-vis data integrity issues.

Answer 3

In response to your edit: As far as I know, this is not possible.

But, what you can do, is create a class that is known by NHibernate, and that just contains the properties that you're interested in.

For instance, a 'TaskView' class which just contains certain properties of the 'Task' class.
You'll have to 'import' the TaskView class in a hbm.xml file, so that NHibernate knows about this class (see the import mapping).
Then, you can use a Projection to convert the 'Task' to a TaskView instance. When you have a look at the query that NHibernate generates, you'll see that it will only retrieve the columns necessary to populate the TaskView class.

Something like I've posted here as well: http://stackoverflow.com/questions/532483/nhibernate-and-collection-counts

Answer 4

Have you tried setting your default constructor to set the default values? As this is an application side issue, it is up to you to address. Btw, why do you need a constructor for each permutation? Hibernate will use either the all argument constructor, or the no argument constructor and then use the setters, so there is no need to do all this work. Actually it can do more than that if I recall correctly it can even set all private fields with no setters if configured correctly though byte code manipulation magic.