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.

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.

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://nhibernate.info/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.