CODEWITHSUNDEEP
nhibernatefluent-nhibernate
Chen Kinnrot
Chen Kinnrot

Reputation: 21025

Select partial data from a DB table using nhibernate

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).

Upvotes: 2

Views: 1680

Answers (2)

Erik Öjebo
Erik Öjebo

Reputation: 10871

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?

Upvotes: 5

sirrocco
sirrocco

Reputation: 8055

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).

Upvotes: 1

Related Questions