Projections in NHibernate

Question

suppose in an entity there are attributes id, username, age, address. Now I just want id and username and I use this code for it.

Projections enable the returning of something other than a list of entities from a query.

var proj = Projections.ProjectionList()
    .Add(Projections.Property("Id"), "Id")
    .Add(Projections.Property("Username"), "Username");

var list2 = DetachedCriteria.For<User>()
    .Add(Expression.Eq("Username", "lachlan"))
    .GetExecutableCriteria( sessionFactory.GetCurrentSession())
    .SetProjection( proj )
    .List();

How will I retrieve the values. In which object will these value be taken.

Answer 1

Unless a result transformer is used, a projection will result in a list of anonymous objects with the projected values. This would be sufficient for databinding.

For other uses, you want to set a result transformer which will create objects of a known type. The AliasToBeanTransformer will create an object of the specified type for each row, and set its properties to the row values.

If you know the type of the results, you can use the generic List<T>() method.

var proj = Projections.ProjectionList()
    .Add(Projections.Property("Id"), "Id")
    .Add(Projections.Property("Username"), "Username");

var list2 = DetachedCriteria.For<User>()
    .Add(Expression.Eq("Username", "lachlan"))
    .GetExecutableCriteria( sessionFactory.GetCurrentSession())
    .SetProjection( proj )
    .SetResultTransformer(Transformers.AliasToBean(typeof(Result)))
    .List<Result>();

Result transformers can also be used on SQL and HQL queries.

list2 = Session.CreateSQLQuery("select Id, Username from user_table")
    .SetResultTransformer(Transformers.AliasToBean(typeof(Result)))
    .List<Result>();

list2 = Session.CreateQuery("select Id, Username from User")
    .SetResultTransformer(Transformers.AliasToBean(typeof(Result)))
    .List<Result>();

In these examples the the Result class does not need to be a mapped class, and must have the selected properties.

partial class Result
{
    public int Id { get; set; }
    public string Username { get; set; }
}