How to select only a few columns in my NHibernate query?

Question

I have one class to display one table; Unfortunately, this table has 110 + columns, and queries take a lot of time, especially when most of the time I want to see only 10 columns.

My problem is that requests are dynamically generated based on what the user wants to see. I cannot create different mappings with different columns, because there would be so many combinations. I use the criteria API to generate queries. Can I also use this to select only the columns that the user wants? Or some other method?

thanks

Answer 1

Use ProjectionList to select the columns you want. See here for examples.

Answer 2

Ease of use LINQ (if you are using NHibernate 3.0 or later):

 var products = from p in Session.Query<Product>() where // ...some query (snip) select new { Name = p.ProductName, Description = p.ShortDesc, Price = p.Price, Units = p.Quantity }; 

In addition, if you use HQL, you can simply select the columns you want to use T-SQL, but use Transformer to get a strongly typed object:

First create a class with narrowed columns:

 public class ProductReport { public string Name { get; set; } public string Description { get; set; } public decimal Price { get; set; } public int Units { get; set; } } 

Then your request:

 string hql = "select p.ProductName as Name, p.ShortDesc as Description ...(snip) " + "from Product p " + "where ...some query (snip)"; IQuery query = Session.CreateQuery(hql) .SetResultTransformer(Transformers.AliasToBean<ProductReport>()); IList<ProductReport> products = query.List<ProductReport>(); 

Just make sure that the aliases in the request (as a name, as a description, etc.) match the property names in your class.

Answer 3

In addition to the example provided by Tim, you can do something like this:

 IList selection = session.QueryOver<Cat>() .Select( c => c.Name, c => c.Age) .List<object[]>(); 

The above example was taken from: http://nhforge.org/blogs/nhibernate/archive/2009/12/17/queryover-in-nh-3-0.aspx