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
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
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.
Use a ProjectionList
to select the columns you want. See here for the examples.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With