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

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 differ开发者_运维知识库ent 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

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.

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

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