static void

NHibernate Queries

NHibernate's methods of querying are powerful, but there's a learning curve. Longer term, Linq is the way to go, although it may not be as capable as Linq2Sql or Entity Framework (Hql and Criteria are arguably superior for advanced scenarios anyway).

Lists with restrictions

//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();

Single Results

//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);

MultiQuery

You can batch multiple queries in a single call. The Hql equivalent would use Multiquery; Criteria uses MultiCriteria.

//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];

Joins

In criteria, use a nested criteria or alias.

//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>();

Subqueries

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>();

Projecting to a DTO

See above for projections for aggregations such as counts. The generated SQL only selects the required columns- not everything. The Transformers.AliasToBean is an ugly relic of the port from Java.

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>();

In Hql, you can either have fun with object arrays, or you have to use a mappings import on the DTO.

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 name = (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 class="Northwind.Dto.ProductLite, 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>();