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.

Answer 2

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: NHibernate and Collection Counts

Answer 3

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.

Answer 4

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 5

I asked a similar question and NHibernate seems to lack the most common feature used by applications since the creation of SQL. The ability to select only some columns. This is really corny and if I had been on the project I would of refused any logic that requires you to jump through a hoop and upside down and all around just to select only fields. Even if there is an answer to it I find hours of research just too complicated for something so simple. I know its available in Nhibernate's linq provider using projection but for extremely complex queries it is not possible so select only specific fields. Either all of them or you must start creating DTOs/Models that are not the original ones which in my view should not have to be duplicated because a user just wants less records. An exclusion/inclusion list for regular sql based queries (not linq) is a must. I wish these ORMs would just do the most basic things first. Simply ignore filling in the model if the fields are not returned in the result set from the database. I hope someone can give a simple solution/hack/workaround.