Select partial data from a DB table using nhibernate

Question

i have a complex entity, very very heavy. i want to select only the id and the name of this entity from the db for a better performance. how do i do that with nhibernate (or fluent nhibernate).

Answer 1

You have 2 choices :

when using ICriteria - use (syntax might not be correct but you get the picture)

SetProjections(Projections.ProjectionList.Add(Projections.Property("prop"))
                                     .Add(Projections.Property("prop1")))

when using hql

select c.Col1,c.Col2 from table c

Both of them will return a list of array lists - or something like that - you then have to traverse it and build your .. dictionary (or whatever you have).

Answer 2

There are a few different possibilities.

Create a New Entity

One possible solution is to create a new entity and map it to the same table, but only map the columns that you want (id and name). This is quite flexible and lets you use that entity as any other entity. The problem is that you introduce some duplication.

Using HQL

Another solution is to use projections. With HQL you can use the select clause to specify which columns to retrieve. If you want a proper entity instance as the result from the query and not an array of objects, you can create a custom constructor for your class and use that in the HQL query.

session.CreateQuery("select new Foo(f.Id, f.Name) from Foo f").List<Foo>();

Using the Criteria API

If you want to use the Criteria API instead of HQL, you can use the SetProjection method. If you want a proper entity from the query and not an array of objects, you can use the AliasToBean result transformer.

session.CreateCriteria(typeof(Foo))
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("Name"), "Name")
        .Add(Projections.Property("Id"), "Id"))
    .SetResultTransformer(Transformers.AliasToBean(typeof(Foo)))
    .List();

The criteria example code is borrowed from the following question, which might be of interest: NHibernate - Only retrieve specific columns when using Critera queries?