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

Question

I have a one class to one table mapping; unfortunately this table has 110+ columns, and queries take a long time process, especially when most of the time I only want to view <10 columns.

My problem is that the queries are dynamically generated based on what the user wants to look at. I can't really create different mappings with different columns because there would be a very large number of combinations. I'm using the criteria API to generate the queries. Can I also use this to only select the columns the user wants? Or some other method?

Thanks

Answer 1

Easy to do with LINQ (assuming you're 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
               };

Also, if you're using HQL, you can just select the columns you need similar to using T-SQL, but use a Transformer to get a strongly typed object back:

First create a class with your narrowed down 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 query:

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 sure make the aliases in your query (as Name, as Description etc.) match the property names in your class.

Answer 2

In addition to the example Tim gave you can do something like this:

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

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

Answer 3

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