NHibernate queries

• Lists with restrictions
• Single Results
• MultiQuery
• Joins
• Subqueries
• Projecting to a DTO

//directly get by id (see also Load<> – loads proxy)
var category = session.Get<Category>(2);
 
//hql
var hqlQuery = session.CreateQuery(“from Product p where p.Category.Id = ? order by p.Id”)
    //set the parameter
    .SetInt32(0, 2)
    //second page of 10
    .SetFirstResult(10).SetMaxResults(10);
 
var list = hqlQuery.List<Product>();
 
//criteria
var criteria = session.CreateCriteria<Product>()
    //”Restrictions” used to be “Expression”
    .Add(Restrictions.Eq(“Category.Id”, 2))
    //ordering
    .AddOrder(NHibernate.Criterion.Order.Asc(“Id”))
    //paging, 2nd page of 10
    .SetFirstResult(10) //zero based
    .SetMaxResults(10);
 
var list2 = criteria.List<Product>();
 
//using NHibernate.Linq
var linq = (from product in session.Linq<Product>()
    where product.Category.Id == 2
    orderby product.Id
    select product)
    .Skip(10).Take(10);
var list3 = linq.ToList();

//counts
var hqlCount = session.CreateQuery(“select count(*) from Product p where p.Category.Id = ?”)
            .SetInt32(0, 2);
var count1 = hqlCount.UniqueResult<long>(); //always a long
 
var cCount = session.CreateCriteria<Product>()
        //add rowcount projection – NB: RowCountInt64 for long
        .SetProjection(Projections.RowCount())
        .Add(Restrictions.Eq(“Category.Id”, 2));
var count2 = cCount.UniqueResult<int>();
 
var count3 = session.Linq<Product>().Count(p => p.Category.Id == 2);

//you can also use separate ICriterias with .Future / .FutureValue
IMultiCriteria multiCriteria = s.CreateMultiCriteria()
    .Add<Product>(
        s.CreateCriteria<Product>()
        //”Restrictions” used to be “Expression”
        .Add(Restrictions.Eq(“Category.Id”, 2))
        //ordering
        .AddOrder(NHibernate.Criterion.Order.Asc(“Id”))
        //paging, 2nd page of 10
        .SetFirstResult(10) //zero based
        .SetMaxResults(10)
        )
    .Add(
        s.CreateCriteria<Product>()
        //add rowcount projection – NB: RowCountInt64 for long
        .SetProjection(Projections.RowCount())
        .Add(Restrictions.Eq(“Category.Id”, 2))
        )
    ;
var criteriaResults = multiCriteria.List();
IList<Product> products = (IList<Product>)criteriaResults[0];
int criteriaCount = (int)((IList)criteriaResults[1])[0];

//no join, it knows Id is on Product
var list1 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq(“Category.Id”, 2))
    .List<Product>();
 
//for any other category properties create nested criteria
var list2 = session.CreateCriteria<Product>()
    .CreateCriteria(“Category”)
        .Add(Restrictions.Eq(“CategoryName”, “Condiments”))
    .List<Product>();
 
//use alias to flatten
var list4 = session.CreateCriteria<Product>()
    .CreateAlias(“Category”, “c”)
    .Add(Restrictions.Eq(“c.CategoryName”, “Condiments”))
    .List<Product>();

var hqlList = session.CreateQuery(
    @”from Product p where p.Id in
        (select n.Id from Product n
         where (n.UnitsInStock = :units
         or n.Discontinued = :dis))
         and p.Category.Id = :cat”)
    .SetInt16(“units”, (short)0)
    .SetBoolean(“dis”, true)
    .SetInt32(“cat”, 2)
    .List<Product>();
 
//DetachedCriteria is easiest way to create subquery
var notForSale = DetachedCriteria.For<Product>(“noSale”)
    //for subquery you must project
    .SetProjection(Projections.Property(“noSale.id”))
    .Add(Restrictions.Disjunction()
        .Add(Restrictions.Eq(“noSale.UnitsInStock”, (short)0))
        .Add(Restrictions.Eq(“noSale.Discontinued”, true)))
    .Add(Restrictions.Eq(“Category.Id”, 2));
 
var criteriaList = session.CreateCriteria<Product>()
    //the id must be in our subquery select
    .Add(Subqueries.PropertyIn(“Id”,notForSale))
    .List<Product>();

var proj = Projections.ProjectionList()
    //projected mapped class property to alias of dto property
    .Add(Projections.Property(“ProductName”), “ProductName”)
    .Add(Projections.Property(“c.CategoryName”), “CategoryName”)
    .Add(Projections.Property(“UnitsInStock”), “Units”);
 
var result = session.CreateCriteria<Product>(“p”)
    .Add(Restrictions.Gt(“UnitPrice”, 10m))
    .CreateAlias(“Category”, “c”)
    .Add(Restrictions.Eq(“Category.Id”, 2))
    .SetProjection(proj)
    .SetResultTransformer(
        NHibernate.Transform.Transformers.AliasToBean(typeof(Northwind.Dto.ProductLite)))
    .List<Northwind.Dto.ProductLite>();

IList results = session.CreateQuery(
     @”select p.ProductName, c.CategoryName, p.UnitsInStock
      from Product p join p.Category c
      where p.UnitPrice > 10 and c.Id = 2″).List();
 
foreach (object[] row in results)
{
    string style=”COLOR: blue”>string)row[0];
    string category = (string)row[1];
    var units = (short)row[2];
    var dto = new Northwind.Dto.ProductLite(name, category, units);
}
 
//need to import it
//<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2″>
//  <import Northwind” />
//</hibernate-mapping>
var results2 = session.CreateQuery(
     @”select new ProductLite(p.ProductName, c.CategoryName, p.UnitsInStock)
      from Product p join p.Category c
      where p.UnitPrice > 10 and c.Id = 2″)
     .List<Northwind.Dto.ProductLite>();